Configuring SQL Server Log Shipping using SSMS

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
• Steps
• Conclusion

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:

Configure SQL Server Log Shipping Environment

• Connect to the primary server.

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

• 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.

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

• Click on Add button under Secondary server instance and database

• Click Connect button to connect to the secondary server instance

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

• 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.

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

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

• 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.

• 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.

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

Also Read: Move Log Shipping Secondary Database File

Conclusion

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.