If you’ve never configured log shipping within SQL Server it can seem to be a bit of a challenge. In reality, it’s actually pretty straight forward. However, there are a few steps that can be a little confusing if you’re new to log shipping. For that reason I have decided to provide a step by step guide to help anyone out there who is looking to configure log shipping between two SQL Servers.
There are a couple things that need to be in place before you begin configuring log shipping. For starters, you’ll need a directory that is accessible to both servers for copying the transaction log backup files. You’ll also want to make sure the SQL Server Agent service is running on both servers. Most importantly, you need to make sure the database on the primary server (the server from which the transaction logs will be shipped) is set to the Full or Bulk-Logged recovery model.
For the purposes of this guide I will be working with a newly created database that I have named LogShipTest. Though you can change the recovery model from the Options page of the database properties window, I find it is easier to do by using one of the following scripts.
To put the database in the Full recovery model use this script, though you will need to change the database name to match the one you are using.
ALTER DATABASE [LogShipTest] SET RECOVERY FULL WITH NO_WAIT
To put the database in the Bulk-Logged recovery model use this script. Again, you’ll need to change the database name.
ALTER DATABASE [LogShipTest] SET RECOVERY BULK_LOGGED WITH NO_WAIT
Once the primary database recovery model has been set it’s time to start configuring log shipping to the secondary database.
To get started, locate the database on the primary server, right-click it and select Properties. Under the Page list select Transaction Log Shipping.
Click the check box to Enable this as a primary database in a log shipping configuration. This will allow you to begin configuring the primary database.
If you missed the above step and the database is not in Full or Bulk-Logged recovery model you will receive the following error. Simply click OK and go back and run the script or update the recovery model from the Options page on the database properties.
Assuming you did not get the above error or have resolved it, the next step is to configure a transaction log backup. To do this click Backup Settings.
The first thing you’ll need to do is specify a backup folder under the Network path to backup folder field. This is where SQL Server will put the transaction log backups which will be picked up by the secondary and restored. As mentioned previously, this path needs to be available to both servers.
The default schedule will have the transaction log backup running every 15 minutes. For the purposes of this example I will not be changing this but if you need it to run more frequently or less often you can easily change this by clicking Schedule and making any desired changes.
The final step on this page is to configure the backup compression setting. In most cases I would recommend having compression enabled. If you have database compression enabled on the server then you can select Use the default server setting. Otherwise I would recommend selecting Compress backup. Depending on the seize of your database this could make a significant difference in the time it takes for the backups to run and restore.
Once complete click OK to return to the previous screen.
In the next step we will configure the Secondary database. To get started, click the Add… button under Secondary databases.
The first step is to connect to the secondary server. In order to do this simply click Connect and specify the server. Once that is done the grayed out portion of the window containing the three tabs (Initialize Secondary Database, Copy Files, and Restore Transaction Log) will be available.
Now, depending on whether the database exists on the secondary server will determine which option you should select on the Initialize Secondary Database tab. If the database exists and is already populated from a recent backup you should be able to select the option No, the secondary database is initialized. However, I find it is generally a better idea to select the first option to Generate a full backup of the primary database and restore it to the secondary database (and create the secondary database if it doesn’t exist). This option covers pretty much any scenario that could exist and is definitely the safest bet.
If you choose the first option you can also click on Restore Options… to manually set the folder where the data and log files will reside on the secondary server. Though not always necessary, it prevents you from having to go move them later.
For the next step, click the Copy Files tab.
Here you will need to specify the destination folder for the copied log files. As indicated, this folder usually resides on the secondary server. Though not absolutely necessary, as you could use a shared network path, I find putting these on the secondary is much more efficient.
When specify the path, you will likely need to provide the fully qualified domain name (FQDN). I would also recommend not putting these on the C:\ drive unless you do not have another option. For testing and learning it probably doesn’t matter but if you’re deploying log shipping on a large database, you run the risk of running out of memory if you don’t manage it properly. And though it should go without saying, I’m going to say it anyway. Running out of memory on your C:\ is very, very bad.
The next (and almost final) step is to configure the options to Restore the Transaction Log. The main piece here is to set the Database state when restoring backups.
If you select No recovery mode the secondary database will be in Restoring state during the restore and cannot be used for read operations during that time. This mode is known as
If you select Standby mode the secondary database will be in read only mode during the restore and can be used for read operations during this time. For most cases I generally recommend selecting this option as it allows both users and reports to still have access to the database. However, you also want to select the checkbox to disconnect users in the database when restoring. This does kick users out initially during the restore but is generally low impact. This allows SQL to have exclusive access to the database when kicking off the transaction log restore.
Once you have selected the desired mode click OK to return to the Transaction Log Shipping main page.
At this point you are essentially finished configuring transaction log shipping. You can click OK to kick off the process of SQL building the Agent Jobs that will handle everything. However, there is one additional step I would recommend before doing this. It is highly recommended to configure a Monitor server instance which will allow you to be notified should one or more of the jobs which handle log shipping fail.
To do this simply click the checkbox next to Use a monitor server instance and then click Settings.
Here you will connect to the secondary server. I recommend leaving the Monitor connections option set to By impersonating the proxy account of the job but if you have a particular service account configured with the appropriate permissions you can assign that here.
Additionally, you can also specify how long to retain the records for job history. I tend to leave this set to the default 96 hour period unless there is a specific reason to keep them longer. Once complete click OK to return back to the Transaction Log Shipping main page.
Now click OK to complete the Log Shipping configuration and kick off the setup process.
The process of building the necessary Agent Jobs shouldn’t take too long, usually less than 20 seconds. Once complete you should see the following screen.
If all options are not shown with a green check you may have missed a step or improperly configured something. If that is the case simply go back into the settings and review the applicable step.
Once the process is complete you can verify the necessary agent jobs were created by navigating to the SQL Server Agent and expanding the Jobs folder. Unless you renamed the jobs during setup, which we did not do, they should all start with LS. You should see jobs created on both the primary and secondary server as shown below:
Once you’ve gone through this process a couple times you’ll see how easy it really is to get log shipping up and running. It’s a very reliable method for automating the backup and restore process to allow for a failover copy of any given database. I hope this guide has provided you with an in-depth understanding of the transaction log shipping process and how to configure it in your SQL Server environment.