Sql-server – Can SQL Server database be changed from NoRecovery to Standby

log-shippingsql server

I'm receiving a log shipped database (and daily transaction logs) from an outside vendor — to our FTP. No internal access allowed. No connection between the two databases.

I've restored the database (and transaction logs) "with NoRecovery" to allow further transaction logs to be applied, but it needs to be in Standby mode to allow for Users to read the data.

Is there a way to convert a database from NoRecovery to Standby? [So that future transaction logs can be applied]

Best Answer

Yes, you can restore the logs by changing the settings for the Restore Job to be set to STANDBY, instead of NORECOVERY. This would provide a degree of Read Only access until the next restore is run.

If you are using the log shipping functions that come with SQL Server you use the settings shown by Ali Razeghi for using sp_change_log_shipping_secondary_database.

If you have rolled your own log shipping process (which many people have done) then you are already controlling the restore process yourself.

EDIT: Since you restore the secondary one time a day, the READ ONLY window will be most of a day. So that eases the scheduling issue.

Since the procedure sp_change_log_shipping_secondary_database is not working, either (1) you ran this on the primary instead of the secondary server, or (2) your organization made their own custom log shipping process.

EDIT: If you are in the (2) state, then please include the details of the secondary server's Log Shipping Restore Job.

However, a database in NORECOVERY can be updated to STANDBY like this:

RESTORE DATABASE [MyDatabase]
WITH STANDBY = N'D:\MSSQL\Backup\MyDatabase_RollbackUndoFile.bak'

Any time the Restore Job recovers a series of LOG files, it will disconnect any STANDBY users that are still connected.

Also see Kendra Little's post: Reporting From a Log Shipping Secondary in STANDBY mode Contained in here post is a mention of the problem that you have run into with restoring a 2008 database to a 2008 R2 Secondary.

You can’t use “STANDBY” Mode if your Secondary is a Higher Version

I was a little sad when I learned this one years ago. I had a SQL Server 2005 instance that was the log shipping primary. The log shipping secondary was made readable so that developers could check out production data if needed without having access to production.

This is because the database pages need to be upgraded to the 2008 R2 level in order to be readable by STANDBY, but that is a violation of keeping the Secondary exactly like the Primary.

Really the only route to getting STANDBY mode on your secondary is to upgrade your SQL Server 2008 to SQL Server 2008 R2 so as to match the secondary. (Or (if you want to modernize) to upgrade to a higher version for both primary and secondary.)

Related Question