Microsoft SQL Server 2005 offers two primary database backup mechanisms -- full backups and differential backups. You'll need to weigh the costs and benefits of each when scheduling backups of your organization's data.
Full backups do exactly what the name implies. They store a full copy of the database on any supported backup media. SQL Server 2005 supports the Microsoft Tape Format (MSTF) for backups to tape, disks and named pipes. Full backups can be performed without taking the database offline, but they consume a large amount of system resources and may have a noticeable impact on database response times.
Differential backups are designed to compensate for the large amount of time required to perform a full backup. They utilize a similar mechanism to store a copy of only the data that has changed since the last full backup. Most databases contain a large amount of information that changes infrequently. In these cases, differential backups will consume significantly fewer resources than a full backup and may even be able to process without significantly impacting database performance.
It is critical to remember that differential backups only contain data modified since the last full backup. In the event of a database failure, a differential backup alone will be useless. For example, imagine that the XYZ Company performs a full backup each Friday at the close of business and differential backups on Monday through Thursday evenings. If the database were to fail on a Wednesday morning, the database administrator would first restore data from the previous Friday's full backup and then apply Tuesday evening's differential backup to bring the database to its most recently backed-up state.
Once again, the exact mix of full and differential backups depends upon a variety of factors unique to your organization. Be sure to consider the length of time required to perform the backups and the impact that might have on your database performance. If your database is only used during business hours, you may be able to schedule a full backup to run each evening after the office closes. On the other hand, if you are supporting a 24-hour operation, you may only be able to sneak in a full backup on a slow Sunday afternoon and use nightly differential backups as a supplement.
Other technologies new to SQL Server 2005 are partial backups (which ignore read-only filegroups, copy-only backups (which allow you to create a backup file without disrupting the differential backup cycle), and mirrored backups (which allow you to have multiple backup sets for added redundancy).
When developing a disaster recovery plan you should also consider the storage of database backup media. If you backup to tape and then store the tapes in the server room, they're not going to do you any good in the event of a fire that destroys the server room. It's best to keep a copy of the tapes in an accessible, secure offsite location. If you're located in an area prone to earthquakes, hurricanes or other natural disasters, you may also wish to consider sending a copy of your backups to a location in another city.
We've discussed the methods used to backup the contents of your database. Ideally, we'd be able to perform a full database backup each time the database was altered. Unfortunately, this is a pipe dream for most database implementations -- the large amount of time and resources required to perform a backup are prohibitive and limit us to scheduling differential backups on a more infrequent basis -- perhaps daily or even hourly depending upon the circumstances. However, SQL Server does provide a mechanism to compensate for database changes made during the period of time between backups -- the transaction log. We'll discuss transaction log backups in the next section of this article. Read on!