SQL Server Performance Forum – Threads Archive

log shipping role reversal problem

changing Logshipping roles seems easy from BOL…but I ve experienced lots of hurdles in each step….
I am not able to execute: EXEC sp_change_secondary_role
@db_name = ‘test’,
@do_load = 1,
@force_load = 1,
@final_state = 1,
@access_level = 1,
@terminate = 1,
@stopat = NULL
GO when I executed this SP, I got an error: Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Per following http://support.microsoft.com/default.aspx?scid=kb;en-us;294397&Product=sql2k I can resolve the issue above by 1)Ensure that there are no outstanding transaction logs before you execute the sp_change_secondary_role stored procedure. Manually running the RESTORE job on the secondary server before you run sp_change_secondary_role ensures that there are no outstanding transaction logs. 2) You could pass the value of zero to the @terminate parameter (@terminate = 0) while you execute the sp_change_secondary_role stored procedure. However, the user must verify that there are no users connected to the database before the stored procedure is executed to ensure that the RESTORE LOG works successfully.
I would like to choose option 1. However I am not sure whats the best to find which transaction logs need to be applied and how to apply them? any examples would be wonderful.
thanks

Option 1 is better and upto the time of disaster on primary server make sure the Tlog are restored on secondary server, as you can see the list of files according to date & time. Refer to books online for RESTORE LOG statement and point in time restore topics. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Is sql server intelligent enough to recognize which logs it needs or i need to restore with norecovery option?

The user must be intelligent to restore the order of the logs if performed manually and log shipping will take care of the log restores. They will be restored by looking at Last and first LSN between the Log backups. Refer to books online for more information on the Transaction log architecture. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

I see. I was thinking it has Oracle like architecture where it knows where to start from.

IN the log shipping process the system will take care of that and if any breakup occurs you must restore Tlogs from the last database restore onwards. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

]]>