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:

    1. Click Connect and connect to an instance of SQL Server that will be used as a secondary server
    2. 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
    3. 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

Another way to create and schedule a log shipping job is to use ApexSQL Backup – a SQL Server backup and restore management tool. It is a tool that helps user schedule a log shipping job via an intuitive wizard, and also allows one to see all jobs history, schedules and outcomes, or raise an instant email alert on a job fail/success.

ApexSQL Backup is a tool that enables management and automation of the backup jobs while preserving the backup chain for an easy point-in-time restoration – database rollback. ApexSQL Backup fully supports log shipping with copying backup to a network share and restoring on multiple destination servers.

In order to schedule a log shipping job, the following steps need to be executed:

  1. Start the application, and in the main ribbon, click on the Log shipping button to bring up the log shipping wizard:

  2. In the Configuration of the wizard, specify the following:

    1. Select SQL Server that hosts a database used as a source
    2. Select a database that will be used as a source
    3. Select a network location – path to a shared folder accessible by destinations

    Click on the Add button to bring up the Log shipping destination setup dialog

  3. In the Log shipping destination dialog specify the following

    1. Select SQL Server that hosts a database used as a destination
    2. Select an existing database that will be used as a destination, or enter a name for a new database that will be created during a restore job
    3. Choose a recovery mode
    4. Select a local folder that will be used as a storage for copied backups and undo files
    5. Select folders for the data and log files

    Click OK to close the dialog.

  4. To add more destination databases, click on the Add button again, and repeat the previously described steps.

    When all destination databases have been added, click on the Next button to continue through the wizard.

  5. The next step of the wizard allows the user to schedule the log shipping jobs. Both backup and restore jobs can be scheduled by clicking on the schedule button in an appropriate field

  6. In the Schedule wizard for both backup and restore jobs, the user needs to specify when, and how often will a job occur (frequency), as well as chose a job duration (validity period). After the schedule parameters have been set, click on the Create button to finalize the schedule(s) and go back to the previous screen

  7. After the schedules have been set, it is possible to set up an automatic email notification on job completion

  8. To complete the wizard, simply click on the Next button, and wait for the schedules to be submitted or executed

  9. With this, the backup and restore job schedules of the log shipping process are created, and both jobs will occur in accordance to the schedule’s parameters. Created schedules are now displayed in the Schedules view, with other existing schedules, from where they can be tracked, or alternatively removed

Furthermore, when the scheduled backup and restore jobs occur, they can be tracked and inspected in the Activities tab

April 6, 2015