Types of Database Backups for SQL Server 2005

You have several backup options to consider with SQL Server 2005, depending on your DR plan and the baselines you have set for the data center. The following list categorizes these options:

• Full database backup   This operation just backs up the database files, which would include all the committed transactions. Before the data gets transferred to the tape, SQL Server makes sure data in the transaction log is committed to the database.

• Transaction log backup   This operation just backs up the transaction logs. However, the transaction log backup should be part of all database backup operations for full recovery.

• Differential database backup   This operation just backs up the portions of the database files that have changed since the last full backup, or since the last differential backup.

• File/filegroup backups   This operation allows you to make the target of your backup a filegroup, rather than a database or a transaction log.

• Snapshot backups   This operation allows you to take snapshot backups, which are very high-speed backups.

Table 7–2 lists the backup types in relation to the model options.

Table 7–2: The backup types offered by SQL Server’s online backup architecture

Model	Full Database	Database Differential	Log	File Differential	Snapshot
Simple	Required	Optional	Not allowed	Not allowed	N/A
Full	Required or file backups	Optional	Required	Optional	N/A
Bulk-Logged	Required or file backups	Optional	Required	Optional	N/A

Full Database Backups

A full database backup backs up the entire database. The DBMS makes copies of all database objects, users, roles, tables, and so on. You can perform a full database backup using Management Studio, or you can program the backup facilities in the DBMS directory using T-SQL.

When the backup events fire, the DBMS flushes the data pages in memory to disk and then starts copying the data to the backup device assigned to the job. Any transactions that take place in the system after the backup starts are not backed up as part of the database backup. To back up the “late” transactions, you next need to back up the transaction logs. The latter “FYI” is important to remember, or you’ll end up with missing data in your backups.

Transaction Log Backups

The only way you can restore to the last transaction before disaster is by backing up the transaction log, and the transactions in the log might not include any transactions that come in while the log is being backed up. In many respects, you can think of the transaction log as the incremental backup of the database, because in order to restore the entire worth of data (all transactions installed to the database and all transactions still in the transaction log that have not yet been installed to the database), you would first have to restore the full database and then restore the transaction log. Transaction logs are thus only useful with full and so-called bulk-logged data.

In large systems that have a huge amount of transactions underway, the transaction log at times may take longer to back up than the database. In very large systems, the transaction log has a tendency to grow to huge proportions, so you will need to balance your transaction log management requirements with your backup requirements, lest what you do for one countermands what you do for the other.

Transaction log backups are a must in the following situations:

• You cannot afford to lose any data between the last database backup and the point of failure.

• You want to return the database to a specific point in time, say ten minutes before meltdown. It is also possible to restore to a database state a day ago or even a week ago by restoring a database from an earlier backup set (see the section “Backing Up SQL Server” later in this chapter) and then restoring the transaction log recorded soon after that time.

• If the databases are constantly changing, your database backups will become outdated very quickly. Some applications work with a database of a relatively constant size but in which the data changes frequently. A good example is an air traffic information system where for any given day the number of flights for the day remains constant but the arrival and departure information is constantly changing.

• Backing up the transaction log forces some cleanup in the process because the operation truncates the inactive portion of the transaction log for you as part of the backup process.

Transaction Log Backups in Practice

First, the transaction log backup can only work when it is part of a full or bulk-logged recovery model (see the section “SQL Server 2005 Recovery Architecture” earlier in this chapter). Second, backing up a transaction log will only serve a purpose if it is part of an unbroken sequence of transaction log backups, which are made after every full or differential backup. If you lose a transaction log backup, if you miss it, or if your backup media get destroyed, you will have to start the backup set from scratch and start backing up the transaction logs all over again.

Truncation and the Transaction Log Backups

As discussed in Chapter 2, the DBMS truncates inactive portions of the transaction log. The inactive portion of the log is already committed to the database; therefore the “dead” portions of the log are no longer needed.

Truncation is something you define in your management of the DBMS and the database, and it is something you can do manually. However, the DBMS performs truncation automatically after a transaction log has been fully backed up, so any manual truncation would cause problems with the automatic truncation that occurs after backup.

Note 

Understanding truncation log checkpoints is key to managing a backup/restore plan. Accordingly, please see Chapter 2 on transaction log architecture.

When Not to Back Up Transaction Logs

The following rules apply to the backup of transaction logs:

• You should not back up a transaction log until a full or differential backup has successfully completed.

• If you have manually truncated the log just after performing the backup, you will have to back up the database again and then back up the transaction log.

Differential Database Backups

The SQL Server differential database backup only backs up the portions of a database file that have changed since the last database backup. It is important to understand that the meaning of the term “differential” is that the first differential backup has to compare the full backup to the database file, while subsequent differential backups are based on what has changed from the last differential backup.

You should also know that the differential backup is not a substitute for the transaction log backup. If you lost a system seconds after performing a differential backup, you will still have lost all transactions in the log that were not written to the database file on disk. Again, no matter how powerful your system, the latency between receiving the transaction and flushing it out to the transaction log on disk cannot be resolved by any backup technology (see “Establishing Baselines for Disaster Recovery” earlier in this chapter).

Differential backups should be used for backing up data in the middle of regular transaction processing time (and the full backup should take place off peak with longer intervals). On big systems receiving huge amounts of new data and data changes every day, a differential backup may seem like a full backup. In some situations a full backup might be very difficult to do on a huge database, even in the middle of the night.

Use the differential under the following circumstances:

• A small percentage of the full database changes every day.

• You need to perform more regular backups of your databases, possibly during regular hours with users logged in.

You might find that the transaction log backup is more suited to your needs for regular backups, especially during heavy or peak periods of online activity.

File/Filegroup Backups

A file or filegroup backup works just like a database backup, but your target object to be backed up is not the database, per se, but a file or collection of files that could reside anywhere on your network. (See “Filegroups” in Chapter 2.)

In this respect, backing up filegroups is similar to performing a SQL Server 2005 backup using an external backup program that has built-in open files capability. Such backup programs, such as NetBackup, also manage the backups of the transaction logs needed for the backups.

Why perform a filegroup backup? Depending on the technology used, there’s one very good reason. File or filegroup backups and restores may be quicker to perform than regular backups. A very busy database, processing millions of transactions a day, may take forever to back up a database. This is not an uncommon problem in a busy data center, and database backups have often run into the next day, interfering with regular operations. If you have three filegroups, it might be quicker to back up a separate filegroup each day In other words, you would have to back up each filegroup every third day You would still have to back up your transaction logs on a daily basis in order to make a full restore to the point-in-time failure.

File or filegroup restores on a piecemeal basis are also possible. For example, if you lose a drive holding a filegroup, one of many drives holding your spread of database files, you can just restore the files or filegroups that went with the disk. Backing up and restoring files and filegroups is, however, tricky. You should practice on development databases or filegroups or do pilot filegroup backups on dummy databases. You must also be fully conversant with filegroup management because it can be easy to blunder both the filegroup management and the backups (see Chapter 2 for more information on filegroups).

Snapshot Backups

The idea of a snapshot backup is a simple one. Think: “smile, say cheese, and snap” and you’ve got the idea. But in order to take a “photographic” image of a database, the DBMS must allow a process to come in and take the snapshot. Essentially what the snapshot means is that the entire image of the database and the log files is (almost) instantly duplicated and then placed onto tape or another disk.

SQL Server 2005 supports snapshot backup and restore, but the snapshot hardware and software are usually provided by independent hardware and software vendors, such as NetBackup. SQL Server provides the necessary hooks in its backup/restore API, and it includes a virtual backup device, to allow a third party to come in and snap away, while it is fully operational.

The snapshot backups drastically reduce and practically eliminate the need for server resources to accomplish high-speed active backups. And as discussed earlier, this is very important for very large databases where availability is critical. The primary benefits of the snapshot backup are as follows:

• The snapshot backup can be created in seconds, with little or no impact on the server (depending on the hardware and software used).

• The snapshot can be restored just as fast, with little or no impact to the server (depending on the hardware and software used).

• You can back up to tape from another server without having to impact the production system.

• You can use the snapshot to very quickly create a copy of the production database for reporting, testing, or data transformation.

The snapshot backups function and provide the same features as the other type of backups discussed. And the transaction logs can be used with them just as they are with the standard full backup. Backup, restore, tracking, and cataloging takes place as if the backup were a standard backup.

Why then would you not use the snapshot backup and restore functionality all the time? The reason would be cost. The backup devices and software can be very expensive. But for very large databases or in mission-critical situations, the costs are discounted against the need to have 0/0 service level acuity, as discussed earlier.