Tag Archives: configure log shipping in Microsoft SQL Server

Log Shipping configuration for SQL Server

Log Shipping for SQL Server

Log Shipping is a basic level SQL Server high-availability technology
that is part of SQL Server. It is an automated backup/restore process
that allows you to create another copy of your database for failover.

Step 1: Check the Status of databases

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'CMT'

USE [master]

Step 2: Enable and start the SQL Server Agent Service

Step 3: Open SQL server management studio

On the primary server, right click on the database name in SSMS
and select Properties.
Then select the Transaction Log Shipping Page.
Check the “Enable this as primary database in a log shipping configuration” check box.

Step 4: configure and schedule a transaction log backup. Click on Backup Settings

Creating backups on a network share enter the network path or for the local machine you can specify the local folder path.


local: D:\backup

Step 5: we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database

Step 6: Go to Secondary Database Setting:

Initialize secondary Database:
create the data on the secondary server.
You have three options:
create a backup and restore it.
use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

Copy Files
specify the destination folder where it copied


Restore Transaction Log Tab

Two Modes:
1. Standby Mode ( readonly mode used for reporting purpose)
2. No recovery mode

specify the database restoring state information and restore schedule.
This will create the restore job on the secondary server.

Note: You can also add Monitor Server