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

The principle of operation of log shipping is to execute transaction log backups on the primary server and restore them to the secondary server. As a rule, the size of the transaction log backup is small and allows it to be effortlessly transferred and restored.

Since log shipping is easy to configure, it can be a good alternative to replication and mirroring if it is acceptable that secondary servers are read-only. Note that, unlike replication and mirroring, log shipping will always have a temporary gap in terms of data relevance between the primary and secondary servers.

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.