2

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]

3

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.)

  • The received transaction files occur daily, so the restore window is not an issue; however, when attempting to run sp_change_log_shipping_secondary_database receive the error that Database ... does not exist as log shipping secondary. – Carl Porch Oct 29 '15 at 14:25
  • @CarlPorch - Updated options on getting to STANDBY mode after logs shipping in NORECOVERY mode. – RLF Oct 29 '15 at 16:17
  • I realize now that what I was missing was the 'N' in front of the filename! – Carl Porch Oct 29 '15 at 19:02
  • @CarlPorch - Does that mean that you have a solution? – RLF Oct 30 '15 at 14:01
  • I THOUGHT I had a solution -- but NO. The problem is thornier than I initially anticipated. After numerous failures in restoring a log shipped database to Standby (so that future logs could be applied), I went back to the sender of the data and found they were using SQL Server 2008 (whereas, I have SQL Server 2008 R2). I will need a more creative solution. – Carl Porch Oct 30 '15 at 21:22
0

You'll want to use the sp_change_log_shipping_secondary_database stored procedure in the Master database. example would be:

USE [Master]
GO
execute sp_change_log_shipping_secondary_database
@secondary_database = 'DBName'
, @restore_mode = 1
, @disconnect_users = 0 -- or 1 
--Depends on your choice.  Disconnect is safer but more annoying for users

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.