SQL Server Log Analyzer

Configuring SQL Server Log Shipping using SSMS

Stacy Brown ~ Modified: March 21st, 2023 ~ SQL Transaction Log ~ 3 Minutes Reading

SQL server log shipping

As we have discussed in a previous article Transaction log shipping is a simple SQL Server high availability technology that offers disaster recovery protection at the database level. Now, it’s time to know how to configure SQL Server log shipping as well.

Now, in this article we are going to discuss the entire topic with solutions using SQL Server Management Studio. Transaction log shipping contains the primary server and one or more than one secondary server.

Table of Content

Prerequisites to Configure SQL Server Log Shipping

Before you start to configure SQL Server log shipping

  • The primary database needs full recovery or bulk-logged recovery module
  • All the server should have a same case sensitivity setting which involved in transaction log shipping
  • SQL Server 2005 or later edition should be installed
  • You must have a sysadmin membership to configure SQL Server log shipping

As I said earlier, the database must use full or bulk-logged recovery module if it is not in full or bulk-logged recovery module then you can change it by using the following query:

Change recovery model

Configure SQL Server Log Shipping Environment

  • Connect to the primary server.

sql login

  • Right-click on the database in SSMS (SQL Server Management Studio) that you want to use as a primary database and select properties then.

Database proper

  • Select the Transaction Log Shipping page under Select a Page column.
  • Click on Enable this as a primary database in a log shipping configuration box.
  • Click on Backup Setting under Transaction Log Backup to schedule transaction log backup.

database properties

  • Define the backup setting (i.e. path to backup folder, delete file older then, alert if no backup occurs within, backup job, backup compression)

Transaction log backup setting

  • Click on Add button under Secondary server instance and database

secondary database

  • Click Connect button to connect to the secondary server instance

transaction SQL Server log shipping

  • Choose a database or type a new database name in Secondary database box

configure SQL Server log shipping

  • In the next step secondary server gives you the access on three tables, i.e.
    • Initialize Secondary Database tab.
    • Copy File tab.
    • Restore Transaction Log tab.
  • In Initialize Secondary Database tab you have three options to specify the data structure on secondary server as shown below.
    • Generate full backup of the primary database and restore it into the secondary database.
    • Restore an existing backup of the primary database into the secondary database.
    • The secondary database is initialized.

Initialize Secondary Database

  • In Copy file tab you have to specify the path of the folder where the transaction log backup should be copied.

copy files

  • In Restore Transaction log tab you have to specify the database state when restoring database.

configure SQL Server log shipping

  • Check Use a monitor server instance, under Monitor server instance, which will notify us in case of any failure and then click on Setting button.

monitor server instance

  • In Log Shipping Monitor Setting window, click on Connect button.
  • Select the connection method under Monitor connection to be used by a backup, copy and restore jobs connect to this server instance.

log shipping monitor setting

  • To finish the transaction log shipping configuration, click on the ok button.

Also Read: Move Log Shipping Secondary Database File


Transaction log shipping is a simple and supreme solution that provides disaster recovery protection. This topic described step by step process to configure SQL Server log shipping and some important things you had to keep in your mind before you start to the task.