Use log shipping when things go awry in SQL Server

Barrie Sosinsky, Contributor

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

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.