Scheduled Restore. How to set up Log Shipping

Log Shipping is SQL Server technology that is available in Standard/Enterprise editions. The technology may be useful in solving the following problems:

  1. Creation of read-only servers to perform heavy SQL queries and reduce the load on the primary server
  2. Creating backup servers that can be quickly put into operation in the case of a primary server failure
  3. Creating a server with data, that lagging in relevance from the primary server for a given time interval

What is log shipping?

Log shipping is a process that allows to automatically synchronize primary database with databases located on other servers (secondary database). This process involves creation of a transaction log backup on primary server, delivery, and restoration of the backup on secondary server. Due to its speed, simple setup and support, log shipping can be a great alternative to replication or mirroring. However, unlike replication, databases on secondary servers will not be writable, and their data relevance will also lag behind for a fixed time interval (which depends on the frequency of log shipping).

When can it be useful?

Log shipping is a tool, and like any other tool, it can be used for a variety of tasks. It can be useful in solving a number of problems, such as:

  1. Reduce load on the main server by creating a secondary server that processes read-only requests. For example, queries for graphs and reports, or you can use secondary database as a target for ETL
  2. Creating backup (e.i. standby) servers that can be quickly put into operation in case of primary server failure
  3. Creating a server with data, lagging in relevance from the primary server for a given time interval

How it works?

This process can be set up manually through SQL queries. The first step is to create a database on secondary server in standby\norecovery mode. To do this, you need to backup your primary database

BACKUP DATABASE [PrimaryDatabase] TO  DISK = N'c:\PrimaryDatabase.bak'

And restore the secondary server with STANDBY or NORECOVERY option

RESTORE DATABASE [SecondaryDatabase] FROM  DISK = N'c:\PrimaryDatabase.bak' WITH STANDBY= 'c:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SecondaryDatabase_rollback_undo.bak'

Please note that for standby option you need to specify the path to a file that will contain pending secondary database transactions which can be used to roll back the operation. This file is as important as .ldf and .mdf files and cannot be deleted while the secondary database is in standby mode. After restoration on secondary server database will be in standby\read-only mode.

In order to sync databases, you need to create transaction log backup on primary database and execute restore on secondary database. Transaction log backup is performed with BACKUP LOG command

BACKUP LOG [PrimaryDatabase] TO DISK= 'c:\PrimaryDatabase_log.bak';

To perform transaction log backup, the primary database must have full recovery model set.

RESTORE LOG command will help you restore created file:

RESTORE LOG [PrimaryDatabase] FROM  DISK = N'c:\PrimaryDatabase_log.bak’ WITH  STANDBY = N'I:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SecondaryDatabase_rollback_undo.bak'

Transaction log backups contain only changed data since the last transaction log or full backup. Their creation and recovery processes are fast, do not load databases, and they are also small in size.

Each time a RESTORE LOG operation is performed, secondary database will be synchronized with primary database at the time of BACKUP LOG operation.

How do you automate the process?

Although it is not hard to perform backup and restore manually, creating a script that automates this process can be a tricky task.

A good log shipping automation solution should provide not only automated backup and restore, but also delivery of backups to secondary server, follow correct recovery sequence, and send failure notifications.

We will review two tools for log shipping automation below.

Log Shipping can be configured via SQL Server Management Studio

The following things are required to set up log shipping:

  1. Two standalone SQL Server
  2. An available shared folder for both servers

Preparation

  • Recovery model

Log shipping can only be performed for databases with the Full or Bulk-Logged recovery model. To set the Full recovery model, execute the following SQL query:

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT
  • Permissions

It is crucial to provide read/write permissions for a shared folder for the following users MSSQLSERVER and SQLAgent on both primary and secondary servers.

  • Configure SQL Server Agent Launch

By default, the SQL Server Agent service is set to a manual startup type. It is necessary to change it to an automatic. To do this, please follow these steps:

  1. Press “Windows+R” and enter services.msc
  2. Find SQL Server Agent in the list, right-click and select “Properties”
  3. Set “Startup Type” to “Automatic” and click “OK” to save the settings

Log Shipping step-by-step setup

Below is a step-by-step guide on how to set up log shipping between the primary and secondary servers.

  1. Open SSMS and connect to primary SQL Server
  2. Right-click on the database in the “Object Explorer” section, in SSMS and select “Properties”
  3. Select “Transaction Log Shipping”, activate the checkbox “Enable this as a primary database in a log shipping configuration” and click “Backup Settings…”
  4. Specify a shared folder to transfer files between servers. If the folder is located on the primary server, then it is recommended to specify the local path to that folder. When it is set, click “OK”.
  5. Now add the secondary server by clicking on the “Add…” button
  6. Add a connection to the secondary server by clicking on the “Connect…” button
  7. On the “Copy Files” tab, specify the local directory on the secondary server to store log backups. SQL Agent on the secondary server will copy backup files from the shared folder into this directory.
  8. On the “Restore Transaction Log” tab, select “Stand By” mode and “Disconnect users in the database when restoring backups”
  9. By clicking on the “OK” button, the process of creation of a database on the secondary server starts. If everything is successful, the following window appears:

Settings Verification

After Log Shipping activation on the primary server in “Object Explorer” -> “SQL Server Agent” -> “Jobs” a backup job should appear.

And on the secondary server, a job to copy the log file and restore it should appear.

To track the actual use of transaction logs, click on the “View History” on the restore job.

Here, the history of the latest restored logs will be available.

If the needed records are absent, then check the backup and copy job for errors.