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
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