How to configure Transaction Log Shipping for SQL Server

SQL Log Shipping is a simple solution that operates and provides disaster recovery protection at the database level for SQL instances.  The general gist is that the database data/logs are replicated/backed up to a secondary server which is also running SQL and allows the database to failover and start running on the secondary site if there is a problem with the primary site.  It’s nice and simple to get going, so if you have multiple sites and can spare the SQL licenses, it’s definitely recommended.

Before starting this setup, you will need to ensure that you have a network location that you can backup your current SQL database to.  This can be on the same server that your primary instance, but needs to be shared out and obviously accessible.  In terms of a file structure, it would be a good idea to have Instance > Database names.  That way it is easy to manage and you know which database goes where.

It is also assumed that you have created a share on the secondary SQL server, where the logs will be shipped to.  This will be where your fail over data goes to.  The file structure should be the same as the primary server and also shared out.

1. Make sure that your database is in ‘full’ or ‘bulk-logged’ recovery mode.

2. Click on Transaction Log Shipping, then tick Enable this as a primary database in a log shipping configuration

3. Select Backup Settings, and then select the network path location.  This will be where you’re database will be backed up to and should be on the same server as your primary server.