Use log shipping when things go awry in SQL Server

Learn how SQL Server log shipping can help bring up a backup when things go wrong.

To keep a standby server in a state where it can take over during failure (with little difference between it and...

the production server), you may want to use a technique called "log shipping." Log shipping is found in SQL Server 2000 Enterprise Edition and can be set up in the SQL Server Database Maintenance Wizard and applied to a single specific database. Basically, transaction logs on your production server are backed up and then copied or shipped automatically to the standby server, followed by restoration. While using this technique, it is possible to control the time difference between your backups and restores.

You'll also need to transfer your login information so that the standby server can be made active successfully.

When a production server fails, the administrator brings a standby server online manually. The amount of time from system failure to promoting the standby server will depend on the equipment and the transactional load. To minimize the loss of data, data should be backed up, copied and then committed to the orphaned log using the NO_TRUNCATE command, if your connection to the primary server isn't lost yet and if this is still possible.

To learn more about failing over and synchronizing the login information, check the topic "How to set up and perform a log shipping role change" in SQL Server Books Online.

Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield, MA). He has written extensively on a variety of computer topics.

This was first published in March 2005

Dig Deeper on SQL Server Backup and Recovery



Find more PRO+ content and other member only offers, here.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: