SQL Server Log Shipping - Disaster Recovery Mechanism

It is crucial for any business or organization, deploying SQL Server, to ensure that the mission critical data within the SQL databases is available when needed. Also, to ensure that all the SQL Server instances or databases must be up and running with no or minimal interruption. To help the organizations, Microsoft introduced the Log Shipping feature in SQL Server as a disaster recovery solution. In this article, we will discuss the SQL Server Log Shipping feature in detail and see how to configure this feature in SQL Server. 

What is Log Shopping in SQL Server?

Log Shipping is a feature in Microsoft SQL Server that allows replicating the information in databases from one SQL Server instance to another using the database transaction log. The transaction log contains all the transactions in the database.

The Log Shipping process involves taking backup of transaction log files on a primary (production) database server, and shipping the log backups to a secondary (standby) server and restoring them. The goal is to increase database availability by maintaining a backup server that can be used to replace a production server.

Configuring Log Shipping in SQL Server

For configuring log shipping in SQL Server, you need a primary server with a database to back up the transactional log file and secondary SQL Server instances for copying the created transaction log backup file and restoring the transaction log file. 

Before configuring the log shipping feature, consider the following:

  • To enable log backups, you must have a database with Full or Bulk-logged recovery model.
  • You must have a shared folder to make T-log backups available to the secondary server.
  • Make sure the database name and collation setting of server must be same.
  • The log-shipping stored procedures require membership in the sysadmin fixed server role.
  • Shared folder should be there between primary and secondary servers.
  • SQL Server agent must be running.
  • Service account must have all permissions on the shared folder.

Steps to Configure Log Shipping in SQL Server

To configure Log Shipping, you need at least two SQL Servers on the same network. However, it is recommended to have three servers. The third server (known as Monitor Server) is used to monitor Log Shipping. It records all the log shipping details, including history and status of the transaction log backup and restore operations and backup failure alerts. You can configure log shipping using the GUI-based SQL Server Management Studio (SSMS).

Configure Log Shipping using SSMS

To start log shipping using the SSMS, follow the steps below:

Step 1: Enable Log Shipping Configuration and Configure Backup Settings on Primary SQL Server Instance

  • Open SQL Server Management Studio (SSMS) and connect to the primary instance of SQL Server.
  • From the Object Explorer window, expand Databases and then right-click the database to be used as the primary database. Select Properties
  • In the Database Properties dialog box, click Transaction Log Shipping under Select a page.
  • Next, select the "Enable this as a primary database in a log shipping configuration" checkbox.

Use this in checkbox

  • Now, configure Backup Settings on Primary SQL Server instance. For this, click the Backup Settings button under Transaction log backups for setting up and scheduling a log backup.

click the Backup Settings

  • You will see two options to configure the path of the backup:
    • Network path
    • Local path
  • Additionally, you can delete older files. By default, files older than 72 hours are deleted. By default, an alert is raised if no backup occurs within hour.

files older than 72 hours are deleted

Step 2: Add Secondary Server for Log Shipping

  • Once the primary database is configured, right-click the database that you want to use for Log Shipping and then select Tasks > Ship Transaction Logs.

Ship Transaction Logs

  • Under Secondary server instances and databases, press the Add button to add a secondary server instance and database.

Add button to add a secondary server instance and database

  • Now, press the Connect button to connect to the SQL Server instance.

Connect button to connect to the SQL Server

  • Enter the SQL Server name and press Connect.

Enter the SQL Server name and press Connect

  • You have 3 options in the Secondary Database Settings:
    • You can generate a full backup and restore it into the secondary database.
    • You can use an existing backup (if you have one). You need to specify the path.
    • If the secondary database was already restored, you do not need to do anything else.

options in the Secondary Database Settings

  • In the Copy Files tab, specify the destination folder to copy the files. You can also set when the files will expire. By default, the time is 72 hours.

Copy Files tab and specify the destination folder to copy the files

  • In the Restore Transaction Log tab, you have two options:
    • The No Recovery mode: In this mode, the secondary database is not in read-only mode. The users cannot read the data.
    • The Standby mode: It is in read-only mode. The users can read the information but not modify or delete the data.
  • There are also options to delay the restoring backups or send alerts, if no restore occurs within a specified time. By default, it is 0 minutes for the Delay restoring backups at least option and 45 minutes for the Alert if no restore occurs within option.
  • To change the job name and schedule, you can press the Schedule button. This will let you set the execution date, time, and frequency of the backups.

Use secondary database is not in read-only mode

  • Next, set an interval under Alert if no restore occurs within.
  • Click OK to exit the Secondary Database Settings window.

The log shipping will now get configured

Step 3: Configure Log Shipping Monitoring Server

Note: Don’t use primary or secondary server as your Monitoring Server. Use a third SQL Server for monitoring the log shipping process.
  • Right-click the database that you want to use for log shipping and select Tasks > Ship Transaction Logs to start the configuration.
  • Then, try to add the Monitor Server Instance. To do that, press the Settings button in the Monitor Server instance and set up the server.

Configure Log Shipping Monitoring Server

To Conclude

Above, we have explained the process of configuring the Log Shipping feature in MS SQL Server. However, you cannot take a transaction log backup of a corrupted or damaged SQL database. If the database is corrupted, you can use Stellar Repair for MS SQL that can help you repair a corrupted database and restore it to its original form. This purpose-built SQL repair software can even repair severely corrupted SQL database (MDF/NDF) files with complete precision.



Was this article helpful?
FAQs

A. The jobs are automatic tasks that are configured when you set up log shipping. These tasks work on the SQL Server Agent and are used to schedule the Log Shipping tasks. The following jobs are created by Log Shipping:

  • Backup Job: It is created on the primary database. This job backs up the transaction log.
  • Copy Job: The job copies the transaction log backup from the primary server to the secondary server.
  • Restore Transaction Log in the Secondary Database: This job restores the copied backup.
  • Alert: If something fails, this job will raise an alert.
A. Yes, you can modify the log shipping jobs. For this, go to the SQL Server Agent in SSMS and click on job section.

Here are some advantages of SQL Server Log Shipping:

  • Easy to set up
  • Can be implemented in the standard version of SQL Server
  • Can be combined with other high availability and disaster recovery options, such as AlwaysOn Availability Groups, replication, and database mirroring

Here are some disadvantages of log shipping:

Provides database-level availability. If in case you fail to recover the database within the scheduled time, it can cause data loss.

  • Does not support automatic failover from the primary database server to a secondary server instance.
  • Standby database is not readable while restoring transaction logs.
  • Does not work when the secondary database is on a higher SQL Server version and the primary database is on a lower SQL Server version.
About The Author
author image
Monika Dadool linkdin Icon

Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.

Table of Contents

WHY STELLAR® IS GLOBAL LEADER

Why Choose Stellar?
  • 0M+

    Customers

  • 0+

    Years of Excellence

  • 0+

    R&D Engineers

  • 0+

    Countries

  • 0+

    PARTNERS

  • 0+

    Awards Received