Temporarily Change SQL Server Log Shipping Database to Read Only

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


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.


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,
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 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

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 (38208)

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 6, 2015 - 12:38:08 PM - Bruce Back To Top (38137)

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 3, 2015 - 9:51:07 AM - AMAN ANKIT Back To Top (38132)

Manvendra- Very nice article. Well explained.

get free sql tips
agree to terms