Log Shipping between SQL 2005 and SQL 2012, log restore problem

• Bill Ennis

  Valued Member

  Points: 53

  More actions

  June 6, 2012 at 1:52 pm

  #262462

  Hi,

  I am setting up log shipping between 2005 and 2012 in preparation for an upgrade with minimal downtime. I have the secondary initialized along with the primary log backup and secondary copy of the tran logs. The restore is having a problem though.

  I ran the restore job from the command line and receive an error that indicates that sqllogship is running the restore using the WITH STANDBY clause. WITH STANDBY is not allowed when the primary database is SQL 2005 but I don't see of a way to override to WITH NORECOVERY. There doesn't appear to be an option to sqllogship.exe for this. I did run a manual recover of a tran log WITH NORECOVERY after the copy and this was successful so I'm thinking that if I can get the LDRestore job to do the same the log shipping automation will work.

  Thanks

  Bill

• Perry Whittle

  SSC Guru

  Points: 234064

  More actions

  June 6, 2012 at 5:51 pm

  #1497964

  Hi

  Run the following against your secondary database and check the column value for "restore_mode".

  exec sp_help_log_shipping_secondary_database

  @secondary_database = 'secondary_database_name'

  To change the secondary database config use

  EXEC master.dbo.sp_change_log_shipping_secondary_database

  @secondary_database = 'secondary_database_name'

  , @restore_mode = 0

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉