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

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.

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.

Manvendra, Greate article thanks! Wondered if you could point me to information about moving log shipping monitoring from one server to another without having to uninstall and rebuild log shipping for each database.

We have quite a few databases doing log shipping to a disaster site, and that being monitored by a sql 2005 server. We wish to retire the sql 2005 server and shift the monitoring to a newer version of sql server (sql 2012). I'm trying to figure out how to do that without having to entirely remove and reset up log shipping for each database.