How to configure SQL Server Log Shipping 

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

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.

Log Shipping Permissions

        To set up log-shipping, you must have sysadmin access to the server.

Log Shipping Minimum Requirements

            1. SQL Server 2005 or later

            2. Standard, Workgroup, or Enterprise editions must be installed on all server                         instances involved in log shipping.

            3. All servers involved in log shipping should have the same case sensitivity                             settings.

            4. The database must use the full recovery or bulk-logged recovery model.

            5. A shared folder for copying T-Log backup files

            6. SQL Server Agent Service must be properly configured.

In addition, you should use the same version of SQL Server on both ends. It is possible to Log Ship from SQL 2005 to SQL 2008, but you can not do it the opposite way. Also, since Log Shipping will be primarily used for failover if you have the same versions on each end and there is a need to failover you at least know you are running the same version of SQL Server.

Steps to Configure SQL Server Log Shipping

Step 1

    Check that your database is in the full or bulk-logged recovery mode.

Step 2

Right-click the database in SSMS on the primary server and select Properties. Then click on the Transaction Log Shipping Page. Check the "Enable this as primary database in a log shipping configuration" check box.

Step 3

    The next step is to set up and schedule a transaction log backup. To do so, select            Backup Settings...

    If you are creating backups on a network share, enter the network path, otherwise        specify the local folder path. Backup compression was introduced in SQL Server                2008 edition. By specifying the compression option while configuring log shipping,        we can control the backup compression behaviour of log backups. When this step is        completed, the backup job will be created on the Primary Server.

Step 4

    We will configure the secondary instance and database in this step. To configure the        Secondary Server instance and database, click the Add... button. If you want to set up     one to many server log-shipping, you can add multiple servers.

When you click the Add... button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect... button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

Initialize Secondary Database for Log Shipping on SQL Server

In this step you can specify how to 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 for Log Shipping for SQL Server

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

Restore Transaction Log for SQL Server Log Shipping

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

Step 5

In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.

Click on Settings... button which will take you to the "Log Shipping Monitor Settings" screen. Click on Connect ... button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

Step 6

Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.