Richard Waymire

How bad would it be if you did your backups religiously, and then, when you lost data due to some unforeseen circumstance, your backup wasn't any good? How can you guard against this? Well, this tip, excerpted from the InformIT.com

Requires Free Membership to View

web site, discusses this very thing.

If a database becomes corrupt, you might still be able to back it up. However, you might not. You might not even get a warning message about the corruption. You should run some checks before backup begins, and SQL Server runs many checks internally to detect corruption (and possibly even fix it). But if you don't run the consistency checks or don't check the results first, you can't tell whether your backups are good until you attempt to restore them. It's a really good idea to periodically test your backups by restoring one to a test server and ensuring that everything is working as expected. This way, you know whether your backups are good and can periodically test your restoration process. SQL Server 2000 includes the capability to verify the integrity of the backup after it's taken. Besides, who has ever heard of database administrators being fired because they were paranoid with their backup and recovery planning?

And Where Should You Back Up?

You can back up your databases to disk, tape, a network drive, or a Named Pipe. Tape is most people's preferred media but has some significant limitations, not the least of which is speed. Backups to disk are usually the fastest. An alternative is to back up your database to another server's disk over the network. You should back up your disk backups to tape, whether those disk backups are to a local disk or to another server's disk. A typical scenario might be to back up to a network server over a fast network connection (100MB Ethernet, ATM, and so on) and then use Windows 2000's built-in backup program to back up the backup devices or files. An alternative is to purchase a third-party backup utility (which might or might not use the Named Pipe option). Most major brands of backup utilities include an add-in to back up SQL Server databases.

As tape speeds increase, they become the preferred media for backups. The limitation for SQL Server backups (in terms of speed) is typically the speed of the tape drives, not the speed of SQL Server.

To read more of this excerpt, click over to InformIT.com. Registration is required, but it's free.


This was first published in November 2000

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.