Log shipping and error 22029

From: kmkrause2 (kmkrause2_at_discussions.microsoft.com)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 13:55:02 -0700

I am having some trouble using MS SQL Server 2000 log shipping. I can get the
database to fail over and become active on the secondary server, but I want
to also have the primary become immediately available for reverse log
shipping.

After I have run the stored procedures to demote the primary, I have to
detach and attach the database on the secondary to be able to run the stored
procedure to promote the secondary. If I don't the procedure fails (sorry, I
don't have the exact error, but it refers to the databse being unable to
access). I also have to manually run the last copy and restore job to make
sure the final transaction log backup gets applied. Once this is done, the
stored procedure to promote the secondary is successful. I then run the
stored procedure to change the monitor server to reflect the new server roles.

Once the database is failed over, I have to go into the maintenance plan for
the reverse log shipping and assign the new destination server. When doing
this, I receive a message stating that the database on the new destination
server is not in standby mode. If I set the database to Read Only in the
Properties dialog box, I can then configure the new destination server. When
I do this, the log backups work fine and so does the copy job. But the
restore job fails with the following error

Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.

I can't find any helpful documentation of this message. Listed below are the
stored procedures I am running.

USE master
GO
EXEC msdb.dbo.sp_change_primary_role
        @db_name = 'modelcopy',
        @backup_log = 1,
        @terminate = 1,
        @final_state = 2,
        @access_level = 1

USE master
GO
EXEC msdb.dbo.sp_change_secondary_role
        @db_name = 'Modelcopy',
        @do_load = 1,
        @force_load = 1,
        @final_state = 1,
        @access_level = 1,
        @terminate = 1,
        @keep_replication = 0,
        @stopat = null

USE master
GO
EXEC msdb.dbo.sp_change_monitor_role
        @primary_server = 'sqlserver1' ,
        @secondary_server = 'staging3',
        @database = 'modelcopy',
        @new_source = '\\staging3\logshipping'

Any help is appreciated

TIA,
Ken

Relevant Pages

  • Create SharePoint Portal failed.
    ... One mentioned ensuring that SQL Server uses a case ... 13:55:40 Service database server is 'USDC-JOHRIV'. ... Update dbo.propertylist set DisplayName = N'Last name' ...
    (microsoft.public.sharepoint.portalserver)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Web Developers - Happy Hearts And HDTV! - Lockergnome
    ... Certificate on your MSIIS Web server. ... getting data from a database is only half the problem. ... Zend recently started a series about building rock solid code in PHP. ... which provides bulk database conversion. ...
    (freebsd-questions)
  • Config for OLTP system
    ... extrenal disks fo the 60GByte database server. ... IBM Informix Dynamic Server Configuration Parameters ... # BUFFSIZE - OnLine no longer supports this configuration parameter. ...
    (comp.databases.informix)
  • Re: TNS could not resolve the connect identifier
    ... This database resides on Machine A. ... The Web server is running on Machine B. ... Using tnsping is not as good as using a real connection such as via ... client (note that this is terminology that appears in the 10g R2 ...
    (comp.databases.oracle.server)