Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in March 2005

Dig Deeper on SQL Server Backup and Recovery

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.