Change the restore mode of a secondary SQL Server database in Log shipping with SSMS

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

Problem

I wrote my last tip on How to change restore mode of a secondary database in SQL Server log shipping. In that tip I explained the need to the change of restore mode and demonstrated how to change it from "restoring" to "standby" mode. We used T-SQL commands and system stored procedures to make these changes in that tip. In this tip, I will change the SQL Server log shipping secondary databases from "standby" to "restoring" mode by using SQL Server Management Studio (SSMS). Here I will explain the step by step process to change the restore mode of secondary database without reinitializing the log shipping configuration through SSMS.

Solution

As explained in the problem section, in this tip we will be changing restore mode of the secondary SQL Server log shipping database from standby to restoring or no recovery mode using SQL Server Management Studio. Apart from using the GUI in this tip and T-SQL in the previous tip, there is one difference between both tips. All changes we made in the last tip were completed done from the secondary database whereas this tip will allow you to make all these changes from your primary server.

Log shipping secondary database can be kept in two restore modes:

• Restoring - This mode is also known as NORECOVERY mode.
• Standby - This mode is also known as Read-Only mode.

When you use the NORECOVERY mode, the database will be in a restoring state and inaccessible to users, so uncommitted database transactions are not an issue.

When you use the STANDBY mode, database will be in Read-Only state and users can access this database for read operations. Transactions in process on primary server or any uncommitted transaction cannot be read in secondary database.

Steps to change the restore mode of the secondary SQL Server database in log shipping

Step 1: First, let's see whether our log shipping is working fine or not. We will launch log shipping status report to check the status of the existing log shipping configuration. Status should be GOOD to make sure both databases are in sync. To run this report, right click on secondary server name; choose "Reports" and then "Standard Reports" followed by "Transaction Log shipping Status" as shown in the below screenshot. Once you will click on "Transaction Log shipping Status", a report will run and appear in the right pane of SQL Server Management Studio. Below is the screenshot of that report. We can see status is shown as "Good" which means both databases are in sync in the log shipping configuration. We can also get information about last copied and restored files along with time since last copied and restore file was applied.

Step 2: If SQL Server Log Shipping is working fine, now check the secondary database mode either in SQL Server Management Studio or via T-SQL. You can check it by running the command below. Look at the restore_mode column, where restore_mode 1 is for standby mode and 0 for restoring mode. We can see its value is 1 which means the database is in standby mode.

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

We can also check this setting in SQL Server Management Studio by expanding the database folder.  The secondary database will be shown in "Standby" mode as shown below.

Step 3: Since the secondary database is in standby mode, a *.tuf file will also be in the data folder corresponding to this restore mode. The below screenshot is showing the *.tuf file of this database.

Step 4: Now that we are certain that the secondary database is in standby mode, our goal is to change the state to "restoring" mode to prevent read operations and make the database inaccessible to users.

Here we will change this setting in SQL Server Management Studio on primary database server. First connect to primary database server in SQL Server Management Studio. Right click on primary database and then select database property. A database properties window will appear on your screen, now click on "Transaction log Shipping" page in left pane. You can find this operation in the below screenshot.

Step 5: Now focus on the "Secondary server instances and databases" area to see the secondary database along with the secondary server name. You can also see the ellipse i.e. three dots near your secondary database name. Click on ellipse button, another window named "Secondary database settings" will appear on your screen. Now as you can see in below screenshot, there are two tabs in the screen. Now click on "Restore Transaction Log" tab. Here you can see both the restore mode options on this page. "Standby mode" along with "Disconnect users in the database when restoring backups" is already enabled in the below screen shot. This has to be changed to "No recovery mode".

Step 6: Now change this setting to "no recovery mode" by selecting this option on the "Restore Transaction Log" tab as shown in below screenshot.

Step 7: Once you make the change to no recovery mode, click the OK button to apply this change for the log shipping configuration. Then another prompt will appear, click on the OK button.  Another window named "Save Log Shipping Configuration" will appear with the status of change whether the change is success or failed. As you can see our change is applied successfully because status of the below screen is showing success. Now click on close button to proceed.

Step 8: Now, we can run same script in Step 3 to check whether the restore mode of the secondary database is changed to stand by or not. We know 1 is for standby and 0 is for no recovery mode. We can see now the restore_mode is showing 0 which means the secondary database restore mode is changed to a restoring state, but this change will not reflect in SSMS until the next restore job runs successfully.

Step 9: Now go ahead and run the backup, copy and restore job for the log shipping configuration in a sequential manner. Once the restore completes successfully, the *.tuf file will be removed from the data folder and the secondary database status will be changed to restoring mode in SSMS. Please see the below screenshot of the original directory storing the TUF file which is no longer in the file system as shown in Step 4.

Below is screenshot of secondary database mode which is now changed to a restoring state in SSMS.

Step 10: Once all three restore jobs run successfully as mentioned in Step 9, we still need to check whether the Log Shipping configuration is working fine or not.  We can run the transaction log shipping status report to check log shipping status to determine if both databases are in sync or not after this change. Here you can see the time stamp of the last restored file in the below screenshot and time stamp of last restored file in Step 1. It's very much clear that all logs are applying on the secondary database after the changes were made.

Next Steps

• Make sure to test this solution in lower life cycle before replicating it directly to production servers. Also make such changes if it is needed otherwise leave your existing log shipping configuration as it is.
• Build your knowledge with more SQL Server Log Shipping and SQL Server Database Administration 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