RE: Log shipping and error 22029

From: Pankaj Agarwal [MSFT] (pankaja_at_online.microsoft.com)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 19:36:53 GMT

Hello Ken

If I understand correctly, there are 2 issues that you have brought up in
this message -

1. You are unable to configure the primary server to reverse log ship at
the time of failover
2. You are having problems with running the sp_change_secondary_role stored
procedure because you get some error that mentions that the database is in
use.

Let me address these -

1. You should use sp_change_primary_role in the following manner if you
would like to leave the primary database in standby(or norecovery) state
for reverse log shipping :

EXEC msdb.dbo.sp_change_primary_role
        @db_name = 'modelcopy',
        @backup_log = 1,
        @terminate = 1,
        @final_state = 3 --3 = Standby, 2 = Norecovery

This performs transaction log backup and leaves the database in a state
where no further changes can be made. PLEASE MAKE SURE THAT THIS STORED
PROCEDURE COMPLETES SUCCESSFULLY OTHERWISE YOU WILL GET ERRORS WHEN YOU
SETUP THIS SERVER AS SECONDARY. If the output indicates any errors, try
running the stored procedure again. If you continue to get errors, please
post the output that you get from this stored procedure in reply to this
message.

2. Please refer to the following KB articles to see if you are running into
one of the known issues :

294397 BUG: sp_change_secondary_role Fails with Error 3101 if There Are
http://support.microsoft.com/?id=294397

308774 FIX: Sp_change_primary_role May Store Transaction Log Backup in the
http://support.microsoft.com/?id=308774

300497 FIX: Log Shipping: Cannot Change Role From Secondary to Primary When
http://support.microsoft.com/?id=300497

Thank you for using Microsoft newsgroups.

Sincerely

Pankaj Agarwal
Microsoft Corporation

This posting is provided AS IS with no warranties, and confers no rights.