Post reply

Log Shipping - help!!!!

  • March 1, 2006 at 9:30 am

    #170814

    I was wondering whether anyone may be able to help me on a little bit of an issue I have got stuck with?

    I have been trying to configure the SQL Log Shipping between the primary and secondary databases that we have set up in our test environment, both of which are on named instance SQL Servers. However no matter what I do, I can’t get the last step to work. I am able to back up the transaction log on the primary server; I am then able to successfully copy the transaction log back up from the primary server to the secondary server. However when the SQL Agent job runs to restore the transaction log to the Secondary database it errors with the following error message:

    Message = 2006-03-01 15:30:00.71  *** Error: The restore operation cannot proceed because the secondary database 'TestDB' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) ***

    When setting up the Restore transaction Log job, I selected the option “No recovery” however it doesn’t seem to have made any difference! I also tried selecting the “Standby mode” option as well, and it hasn’t made the blindest bit of difference!

    Any suggestions, or a slap round the chops to point me in the right direction would be greatly appreciated!!!

    Cheers,

    will.

  • March 1, 2006 at 9:35 am

    #624039

    When you look at the secondary database in enterprise manager does it say dbname (Read-Only)?

    It could be that the initial restore of your backup was not left in recovery mode.

     

  • March 2, 2006 at 11:27 am

    #624410

    Yes, that what it sounds like to me as well.  When you restore the initial backup make sure you go to the Options tab (if you're using Enterprise Manager) and select "Leave database read-only and able to restore additional transaction logs". 

  • March 3, 2006 at 6:04 am

    #624568

    Thanks very much for your suggestions.

    It turns out that the restore I had performed wasn't with the very latest backup of the Primary Database... doh! School boy error there!! I then went through the Log Shipping Wizard again and selected the option to generate a full backup of the primary database and restore it to the secondary database (and create the secondary database if it doesn't exist). Magically it then worked...

    oops!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply