SQL Server Log Shipping -- Complete Overview.. Paert - III

Next Chapter »

Create Your Backup Devices

This step is an easy one. On the production server you will want to create two backup devices. One will be used to store the database backup, and the other one will be used to store the transaction log backups for the database.

To keep things simple, I name the database backup device after the name of the database, something like, “database_name_backup_device”, and I name the transaction log backup device something like, “database_name_log_backup_device”. Sure these names are a little wordy, but they are self-documenting. Choose whatever names make the most sense for you.

Create a Linked Server

Here’s a step that may confuse some of you, especially if you have not used linked servers before. A linked server allows a stored procedure on one server to be called by a script or stored procedure on another server, among other things.

What you need to do is to create a linked server between your production server and your standby server, so that a script running on the production server can call a stored procedure on the standby server.

The reason you need to create a linked server is because we will need to call two different stored procedures on the standby server from the production server. These two stored procedures are used to restore either the database or the transaction logs automatically (more information in the next section).

It is important when you create the linked server that you assign the proper level of security. Below are two screens showing how I have set up my linked servers using Enterprise Manager.

Once you have created your linked server, you are ready to create the stored procedures on the standby server that will be called from the production server.

Create Stored Procedures to Restore Your Data

The next step is to create two stored procedures on the standby server. One will be used to automatically restore the database, and the other one will be used to automatically restore the transaction logs.

You can give them any names you like, but I have been using these two names for obvious reasons:

• restore_database_backups
• restore_log_backups

Of course, you can use any names you prefer.

You will need to create these two stored procedures in a database that will never be overwritten. You can also create the stored procedures in a database you created just for this purpose. It doesn’t matter as long as you won’t be overwriting them.