Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
There has been a lot written about SQL Server Log Shipping as well as several scripts and techniques for implementing this process. Basically Log Shipping is the process of backing up your database and restoring these backups to another server for failover. To keep the data current and to minimize risk of data loss the process involves using SQL Server transaction log backups. By using transaction log backups the size of the backups are much smaller than full backups and the restore process can be done on a continuous basis. So how do you implement Log Shipping?
There are several ways of implementing Log Shipping. Some of these processes are built into SQL Server and others can be implemented by using third party tools. Let's take a look at some of the options that are available.
SQL Server 2005
In SQL Server 2005 the process of implementing Log Shipping is embedded with the Workgroup, Standard and Enterprise editions of SQL Server. To implement it, right click on a database name and select Properties. On the properties screen there is an option for Transaction Log Shipping which looks similar to the screenshot below. From here you can follow the step by step process to set this up.
SQL Server 2000
In SQL Server 2000 this feature is only available in the Enterprise or Developer editions of SQL Server. This does not exist in the Standard or MSDE editions. To enable Log Shipping with this version, this is setup using the Database Maintenance Plan Wizard and selecting "Ship the transaction log to other SQL Servers (log shipping)" at the bottom of the wizard screen. The following screen shot shows you how you can select this option.
Third Party Tools
In addition to using the built in functions within SQL Server there are third party tools that have built-in log shipping functions. Some of these tools include:
- SonaSoft's - SonaSafe for Microsoft SQL Servers
- Red-Gate's - SQL Backup
- Quest's - LiteSpeed for SQL Server
- Idera's - SQLsafe
In addition to the built-in tools and third party tools, there are several implementations that exist so you can implement your own Log Shipping strategy. As I mentioned above the process is as simple as creating a log backup and restoring it to another instance of SQL Server. This can be as manual or automated as you make it. Here are a few links to other articles that exist on the net that show you how you can setup Log Shipping on your own versus using the built in tools or other third party tools.
- How to Perform SQL Server Log Shipping
- SQL Server Log Shipping
- Log shipping a replicated database
- Simple Log Shipping in SQL Server 2000 Standard Edition
- Custom Log Shipping
- Log Shipping is a pretty simple process to setup and use, so take a look at it closely to see if it makes sense for your environment.
- Look at the scripts that are available on the net to see if they are something you can use or modify to make Log Shipping work for your database servers
- Take a look at the new Database Mirroring feature in SQL Server 2005, this may be a simpler solution to setup a failover server instead of using Log Shipping
Last Update: 2007-01-17
About the author
View all my tips