SQL Server Log Shipping - Disaster Recovery Mechanism

It is crucial for any business, organization, or individual deploying SQL Server to ensure that mission crucial Server instances and databases within them are available when needed.

All the SQL Server instances or databases must be up and running for continuing business with no or minimal interruption. Microsoft SQL Server introduced High Availability (HA) and Disaster Recovery (DR) features to provide organizations with reliable access to their mission-critical data. This blog is focused on covering the 'Log Shipping' high-availability feature of SQL Server. Also, it will discuss the importance of using SQL repair software to perform database restore.

About Log Shipping

Many organizations use the SQL Server log shipping availability feature as a disaster recovery mechanism due to its less complex and simple configuration.

It is the process of 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.

Note: The log backups can be shipped to one or more secondary server instances.

The log shipping process comprises:

• Primary server: SQL Server instance on your production server.
• Primary database: This is the database on the primary (production) server that you want to back up to a secondary server.
• Secondary server: This is the SQL Server instance used to keep a standby copy of your primary database.
• Secondary database: This is the standby copy of the primary database. The secondary database is available for limited read-only access, as the database is either in "RECOVERING" state or "STANDBY" state.
• Monitor server: An optional third SQL Server instance that records all of the log shipping details, including:
  
  
• History and status of the transaction log backup and restore operations
• Backup failure alerts

Pros & Cons of SQL Server Log Shipping – Disaster Recovery Mechanism

Configuring SQL Server Log Shipping – Disaster Recovery Mechanism

IMPORTANT! You cannot take a transaction log backup of a corrupted or damaged SQL database. Using Stellar Repair for MS SQL can help you fix a corrupt database and restore it to its original form keeping the data intact.

Prerequisites

• To enable log backups, you must have a database with only Full or Bulk-logged recovery models.
• You must have a shared folder to make T-log backups available to the secondary server.

Steps to Configure Log Shipping

Step 1: Open SQL Server Management Studio (SSMS) and connect to the primary instance of SQL Server.

Step 2: From Object Explorer window, expand Databases and thenright-click the database to be used as the primary database. Select Properties.

Step 3: When the Database Properties dialog box opens, click Transaction Log Shipping under Select a page. Next, ensure that the "Enable this as a primary database in a log shipping configuration" check box is checked.

Figure 1 - Select Transaction Log Shipping

Step 4: Click the Backup Settings button under Transaction log backups for setting up and scheduling a log backup.

Figure 2 – Open Transaction Log Backup Settings

Step 5: When the Transaction Log Backup Settings dialog box opens, follow the below steps to create a backup job on the primary (production) server:

• If you're taking log backup on a network share, enter the backup folder's network path. But if the T-log backup is stored on the primary server, then specify the local folder path.
• Specify parameters for 'Delete files older than' and 'Alert if no backup occurs within'.
• Setup backup compression while configuring log shipping to control the compression behavior of the T-log backups. To do so, select any of these options:
  • Use the default server setting,
  • Compress backup,
  • Or Do not compress backup
• Click OK to apply all the changes you have made.

Figure 3 - Configure Transaction Log Backup Settings

Once you executed the above steps, the primary server will be configured. Next, you need to configure the secondary server. You can configure the secondary database in the same SQL Server where the primary database is stored. However, you can also choose to configure the secondary instance in a different Server instance.

Step 6: Click the Add button under Secondary server instances and databases to configure the secondary server instances and databases within it.

Figure 4 - Configure Secondary Server Instances and Databases

Step 7: When the Secondary Database Settings window opens,do the following to initialize secondary database for your log shipping environment:

• Connect to a secondary SQL Server instance by clicking Connect.
• Choose a database from the Secondary database drop-down list. Or, type the name of a database you want to create.
• On the Initialize Secondary Database tab, select one of the options you want to use for initializing the secondary db:
• Generate a full backup of your primary db and restore it to secondary db,
• Restore an existing backup of the primary db to the secondary db,
• Or do nothing.

Figure 5 - Initialize Secondary Database

Step 8: Click on the Copy Files tab, and then in the Destination folder for copied files textbox, specify the path of the folder where the log backup files will be copied.

Figure 6 - Copy Backup Files to Secondary Database

Step 9: Click the Restore Transaction Log tab, and under Database states when restoring backups select any of these following restore options:

• No recovery mode: The database is not readable in this mode.
• Standby mode: Provides read-only access to the secondary database.

Figure 7 - Restore Transaction Log

Note: There won't be any delay in restoring the secondary database. However, for data disaster recovery, you can delay restoring backups by selecting an interval as per your requirement.

Step 10: Set up an interval under Alert if no restore occurs within.

Step 11: Click OK to exit the Secondary Database Settings window.

The log shipping will now get configured.

Alternative Solution to Restore SQL Database?

In the worst-case scenario, when you haven't implemented log shipping or any other disaster recovery solution, you can try using Stellar Repair for MS SQL to restore your database to its original state. The software is purpose-built to repair severely corrupted SQL database MDF files, which you can use to restore the database.

Was this article helpful?

YES4

NO0

About The Author

Charanjeet Kaur

Technical writer with over 7 years of experience