How to create a simple database recovery solution using SQL Server log shipping

To prevent accidental data loss, it is always good to ensure that there is a disaster recovery solution available. This can be easily achieved by having a standby copy of a primary database on another SQL Server instance, which can be achieved via log shipping.

SQL Server Log shipping is a solution that provides disaster recovery protection at the database level. A log shipping configuration includes one primary server, hosting a live database, and one or more secondary servers that host database copies. The process is fairly simple – a database is backed up and restored from a primary server to the secondary server(s). At regularly scheduled intervals, a transaction log backup and restore is performed at the primary and secondary servers to keep the databases in sync.

Benefits of log shipping:

• Log shipping is a great solution for off-site reporting or non-real-time disaster recovery at a database level
• Read-only (limited) access to secondary databases is available during the interval between restore jobs
• Log shipping allows a user-specified delay between database synchronizations, which allows retrieval of the unchanged data from a secondary database in a specified time frame

Prerequisites:

• A primary database must use the full or bulk-logged recovery model (if a database recovery model is switched to simple, SQL Server log shipping will stop functioning)
• Before log shipping can be configured in the SQL Server, the transaction log backups must be made available to a secondary server by creating a share of the directory where the transaction log backups are generated into.

To configure SQL Server log shipping, the following steps need to be executed:

1. Navigate to a database you want to use as a primary database in SSMS object explorer, right click on it in and select Properties:
   

   

2. In the Select a page panel, click Transaction Log Shipping and check the Enable this as a primary database in a log shipping configuration check box:
   

   

3. Click the Backup Settings button in the same tab to open the Transaction Log Backup Settings dialog
4. In the Transaction Log Backup Settings dialog specify the following:
   • Provide a network path to a shared transaction log backup folder
   • Specify a local path to a backup folder in the If the backup folder is located on the primary server, type a local path to the folder field if a backup folder is on a primary server. If a backup folder is not on a primary server, this field should remain empty
   • Provide the appropriate parameters for the Delete files older than and the Alert if no backup occurs within options
   • Click on the Schedule button and customize parameters in a backup schedule, and provide a name for a job in the Job name field
   • In the Set backup compression drop menu, choose one of the existing compression settings: Use the default server setting, Compress backup, or Do not compress backup
5. When all options in the Transaction Log Backup Settings dialog have been set, close the dialog by clicking on the OK button:
   

   

6. The Database properties dialog is displayed again. In the Secondary server instances and databases dialog, click the Add button to bring up the Secondary Database Settings dialog, in which you’ll need to do the following:
   • Click Connect and connect to an instance of SQL Server that will be used as a secondary server
   • In the Secondary Database field dialog, specify the name of a database that will be created, or choose existing database from the drop-menu list
   • In the Initialize Secondary database, Copy files, and Restore tabs, choose the appropriate options/settings for the secondary database and click OK

   

7. To monitor this log shipping configuration in SQL Server, the monitor server must be added at this point (it cannot be added later without replacing the whole configuration). Monitor SQL Server instance can be added by checking the Use a monitor server instance checkbox, and specifying server parameters in the Log shipping monitor settings dialog, which can be brought up by the Settings button
8. Click OK to finalize the process

This ensures that there is a copy of a primary database on other SQL Server instance which can be used as a safety precaution in case of a disaster or used for any other purpose, as well as a monitoring database that records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

Even so, there are several cons to this method:

• The log shipping setup wizard can be confusing
• There is no simple monitoring or overview of scheduled jobs or those that have already been completed