Re: Log shipping and error 22029

From: Ron Talmage (rtalmage_at_prospice.com)
Date: 09/22/04 

Date: Tue, 21 Sep 2004 22:40:45 -0700

Ken,

First of all, do not use the option "Allow database to assume primary role"
when setting up log shipping. It just doesn't work.

Reversing the direction of log shipping is a manual process.

1. Demote the primary database on the Main server using
msdb.dbo.sp_change_primary_role
as you have it below. This will make one last tlog backup to disk.
2. Promote the secondary database on the Standby server using
msdb.dbo.sp_change_secondary_role
as you have it below. This will take that one last tlog backup and apply it
WITH RECOVERY.
3. Remove all log shipping that remains (jobs and rows for that log shipping
pair in the ls tables - if there are any.)

At this point you can install log shipping from scratch, the new direction
now being from the primary database on the Standby server to the secondary
database (in norecovery) on the Main server.

Hope this helps,
Ron 

-- 
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@discussions.microsoft.com> wrote in message
news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@microsoft.com...
> 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