Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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.

log shipping status report

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
Check LS secondary db mode by t-sql

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.

Check LS secondary db mode in SSMS

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.

TUF file location

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.

Log shipping properties page selection

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".

Change_restore_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.

Change_restore_mode to norecovery

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.

Change apply on LS configuration

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.

Check secondary database mode after changing it to standby mode

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.

TUF file removal in data folder

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

log shipping status in SSMS in restoring state

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.

log shipping status report after changing restore mode to standby
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.


Last Update:






About the author





More SQL Server Solutions











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     



Tuesday, May 05, 2015 - 10:26:24 AM - Glen Moffitt Back To Top

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.

Thanks!


Tuesday, May 05, 2015 - 4:00:26 AM - Gourang Back To Top

thanks for the tip...It is really useful


Learn more about SQL Server tools