3

Created a transaction log shipping configuration between two SQL Servers (different VMs) using the built in wizard. Backup job runs on primary server, copy and restore jobs run on secondary server.

The restore job fails giving the following message (message is in job history):

Error: The restore operation cannot proceed because the secondary database 'My_DB_Name' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping)

Under my secondary database Properties > Options, I see recovery model is set to Full. What do I need to do to make the restore job run?

  • 1
    You have configured log shipping incorrectly it seems. For restore operation to work on secondary your database must be either in standby mode or norecovery mode. I am sure it is online, can you check – Shanky Nov 21 '16 at 15:05
  • It is most likely online. How do I check the mode of my database? I set restore mode to 1 using dba.stackexchange.com/a/119473/58010 but I guess 1 basically means Full which it already is – joym8 Nov 21 '16 at 15:07
  • 1
    You should see (Restoring...) or (Restoring/Standby...) in management studio. If you don't see either of those, the DB has more than likely been brought online. Once it's brought online, you can't force it into NORECOVERY/Restoring mode without restoring the FULL backup again. FULL recovery is just a recovery model which logs all transactions, therefore allowing you to 'replay' them on the secondary server that you are log shipping to – Kris Gruttemeyer Nov 21 '16 at 15:10
  • 1
    If it is online then it is definitely not in standby/norecovery mode. You can check by SELECT DATABASEPROPERTYEX ('db_name', 'status') – Shanky Nov 21 '16 at 15:10
  • 2
    With log shipping, you have to be careful how you do it and understand the process. Since your secondary DB is ONLINE, you can't do 'my secondary is already initialized'. Is this a large DB, if not, I would just have it do a complete full abckup for you through the wizard. – Kris Gruttemeyer Nov 21 '16 at 15:17
3

As the error indicates you, your secondary database is not ready for log shipping or it is online. Before you start the actual log shipping process, you need to make secondary database ready for the log shipping. Either you can leave the secondary database under restoring mode and then let SQL Server take snapshot of your primary database, or you can take a full and transnational backup of primary database and restore it on your secondary DB using no recovery mode then continue your log shipping process.

  • 1
    Could also restore WITH STANDBY. – Nic Nov 21 '16 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.