0

I have two SQL Server 2008 R2 servers one for PROD and the other for DR. I am trying to add log shipping for a database called School.

Steps so far

  • Back up School database

  • Restore with database using the UI or using the following SQL statement i.e.

    Restore database "School" from disk ='t:\Data\School.bak' with NoRecovery.

  • The result is database is stuck in Restoring

  • If I restore the database with Restore database "School" from disk ='t:\Data\School.bak' with recovery. the database restore completes but the log shipping fails. I have deleted the database and recreated it again using Restore database "School" from disk ='t:\Data\School.bak' with NoRecovery.but it is still stuck in **Norecovery**.

  • Is there a way that I can restore the database so that is not stuck in the restoring state and I can complete the log shipping.

1

So, you only have two options in a log shipping configuration. One is where the database on the secondary is in a "read-only" mode where reads can occur on the database until the next log is restored, kicks the users out, then, it returns back to a read-only mode. The second option, which is the most common, is to leave the database on the secondary in a "restoring" state at all times. When it is time to failover to the secondary, the database is as current as the last transaction log which was backed up, copied, and restored with no-recovery.

0

Please try the following command: RESTORE DATABASE DBName WITH RECOVERY

In case, If you failed with this, then you can also go through this link:

http://mssqllover.blogspot.com/2016/08/database-stuck-in-recovery-mode.html

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.