Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Temporarily Change SQL Server Log Shipping Database to Read Only


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | More > Log Shipping

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem

I want to temporarily use a Log Shipped secondary database for Read Only operations, but Log Shipping was not configured for read only operations.  I don't want to reconfigure Log Shipping, so is there a way to do this without redoing Log Shipping.

Solution

As I explained in a previous tip, the SQL Server Log Shipping secondary database has two modes; the first is standby mode where we can access the database for read only operations and the second is restoring mode where we cannot access the database.

Sometimes there may be a need to temporarily switch the modes and in this tip we will walk through how to temporarily make this change without interrupting Log Shipping.

Step 1:

First let's check the SQL Server Log Shipping status to determine if it is working correctly and is in sync with the primary database. We will launch the Log Shipping dashboard report to check the status. The status should be GOOD to make sure both databases are in sync with acceptable latency. To run this report, right click on the server name in SQL Server Management Studio and choose "Reports" and then "Standard Reports" followed by "Transaction Log Shipping Status". In my example, the log shipping status is GOOD as shown in the below screenshot.

Check log shipping status before chaning the restore mode

Step 2:

Next check the restore mode for the log shipped database on the secondary server. Run the code below to check the restore mode of the secondary database.

SELECT secondary_database,
restore_mode,
disconnect_users,
last_restored_file
FROM msdb.dbo.log_shipping_secondary_databases

We can see the restore mode value is 0 ("Restoring" mode). Now we want to change it to standby so we can access the secondary database.

Check restore mode of secondary database

If the database is in a Restoring state and you try to access the secondary database you will get the below error.

Check DB Accessbility

Step 3:

Disable the log shipping restore job on the secondary server to stop applying the new log backups. This is needed because if the restore job runs after changing the restore mode for the secondary database, this job will revert the change to Restoring mode as per the Log Shipping configuration since we did not change the configuration.  This job needs to be disabled the entire time you are using the database in a Read Only mode.

Disable Restore Job

Step 4:

Run the code below on the secondary server to change the database mode from Restoring to Standby, so the secondary database can be accessed.  In the command below I have specified the UNDO file as "D:\DBA\ROLLBACK_UNDO_Gourang.tuf", you can make this whatever you want.  This file is needed to change the mode to Standby.

RESTORE LOG Gourang
WITH STANDBY = N'D:\DBA\ROLLBACK_UNDO_Gourang.tuf'
GO

Restore Mode changed to standby

The above code will create a *.tuf file and will change the database to Standby mode. Below is a screenshot of the newly created *.tuf file which is needed for a Standby mode database.

TUF created

Step 5:

Check the secondary database mode by running same code we ran in Step 2. As we can see the restore mode has not been changed in the system tables, but the restore mode has changed for the database in SSMS. The system tables did not change, because we did not make any changes to the Log Shipping configuration. Make sure to refresh the databases in SSMS if you do not see the database in a Standby / Read-Only state.

Check Restore Mode

Step 6:

Do whatever read operations you need to do on the secondary database, remember that the database is in a Read Only mode.

Check DB to run reports

Step 7:

Once your done using the secondary database, close all active sessions to the secondary database and re-enable the Log Shipping restore job.

Once the log shipping restore job completes successfully, check the log shipping status by running the dashboard report. As we can see below, the Log Shipping status is GOOD and the last backup has been applied to the database.

LS status after reverting the restore mode to restoring

Step 8:

To verify that everything is back to the way it should be, check the restore mode for the secondary database again.  We can see below that the database is in a Restoring state and the last applied backup file is different than the what we started with in Step 2. 

check LS secondary db restore mode
Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, October 19, 2015 - 9:17:35 AM - Himanshu Back To Top

Good article,  Can we take backup on secondary database in either of the modes, no recovery/standby. actually i want to offload by database backu to secondary server. Primary server is heavy loaded , if i take full backup on primary server it is timing out several queries. Is there any way we can shift daily backups to some other server.


Tuesday, July 14, 2015 - 11:38:16 PM - Deepak Kumar Back To Top

Hi Manvendra,

I agree with Bruce's commect, I was reading this article and was thinking why would it remain in standby, but since you did not configure the log-shipping, it is very nice and well explained article and help the folks to work in log-shipping environment very efficiently. Thanks once again and keep it up.

 

 


Monday, July 06, 2015 - 12:38:08 PM - Bruce Back To Top

On my first reading of this article I didn't understand why it wouldn't remain in standby. Then I realized since log shipping was never reconfigured, the job is set to restore with norecovery which will force it out of standby when it resumes. Thanks for the tip!


Friday, July 03, 2015 - 9:51:07 AM - AMAN ANKIT Back To Top

Manvendra- Very nice article. Well explained.


Learn more about SQL Server tools