Temporarily Change SQL Server Log Shipping Database to Read Only

By: Manvendra Singh   |   Updated: 2015-07-01   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > Log Shipping

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.

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.

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

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.

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

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.

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.

Step 6:

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

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.

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. 

Next Steps

• Test this in a lower life cycle environment first.
• Don't do this on a repeated basis.  If this is something you are going to need to do repeatedly, it would be better to reconfigure Log Shipping.
• Explore more knowledge on:
  • SQL Server Database Administration Tips
  • SQL Server Log Shipping Tips

About the author

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