Database Backup Types in SQL Server 2005

Full, Transaction Log, Differential, Partial, Differential Partial, File and Filegroup, and Copy Only Database Backups

• Full Database Backup
• Transaction Log Backup
• Differential Database Backup
• Partial Database Backup
• Differential Partial Database Backup
• File and Filegroup Backups
• Copy Only Database Backup

Full Database Backup

A full database backup captures the entire database in one fell swoop. It can be used to restore a database to the time when the backup completed. A full database backup is often used during restore operations as a first step in recovering a database. Other backups are applied after the full backup is restored to bring the database closer in time to the actual moment of failure.

Data Loss:

A full database backup will not on its own provide full recovery up to the point of failure. A full database backup for SQL Server 2005 restores the database up to the time when the backup was completed. If you have more stringent data loss requirements – and most organizations will – you need to supplement your full database backups with other types of backups. Your best chance at preventing data loss is to use the full recovery model with transaction logs.

Size:

A full database backup will be the largest type of backup that there is.

Resources Required:

A full database backup requires a lot of system resources and can negatively impact system performance.

Transaction Log Backup

Only available under the full recovery model and the bulk logged recovery model. Transaction log backups allow for point in time recovery since each transaction is captured within the transaction log.

Data Loss:

The use of transaction log backups provides point in time recovery. As far as backups are concerned, the use of full backups combined with transaction logs can provide point in time recovery capabilities and minimize data loss.

Size:

Compared to other types of database backups, transaction log backups aren't very large if they are scheduled regularly.

Resources Required:

Generally speaking, transaction log backups take up very little in the way of system resources.

Differential Database Backup

A differential database backup captures changes (and only the changes) made to the database since the last full database backup. This full database backup which differential backups reference is also called the differential base, since the differential backups are recording changes since the last full database backup was performed. The differential database backup records the changes to the database up to the time when the differential backup was completed.

Data Loss:

A differential backup provides additional protection from data loss than a full database backup, but does not on its own guarantee that some data won't be lost. Your best chance at preventing data loss is to use the full recovery model with transaction logs.

Size:

The differential database backup will likely be a fraction of the size of the full database backup since only changes since the last full database backup are saved. However, if you have multiple differential database backups between each full database backup, each differential database backup will be larger than the one before it, since all differential database backups record ALL changes since the last full database backup.

Resources Required:

The overhead required to perform a differential backup is significantly less than what is required for a full database backup since only the changes since the last full database backup are being backed up.

Partial Database Backup

Partial backups were introduced for SQL Server 2005. Partial backups will backup the primary filegroup, any read/write file groups, and any read-only files that are specified during the backup. The partial backup is designed to not backup any data that is placed in a read-only file group. So, you can use a partial backup to backup your entire database – except for the read only data. Partial backups are meant to be used in a simple recovery model situation, although they can be used in a full recovery model situation as well.

Data Loss:

The partial backup provides bare minimum protection from data loss. The partial backup serves as a base for differential partial backups – which can be applied to the partial backup to bring the database closer to a point in time near the actual database failure. A full recovery model with transaction logs provides the best chance to restore the database up to the point of failure.

Size:

A key advantage of using a partial backup as opposed to a full database backup is the reduced file size. The read only filegroups only have to be saved once, therefore the partial backups will be smaller than what would be required for a full database backup.

Resources Required:

The strain on the database will likely be much less for a partial database backup than it is for a full database backup since specified read only filegroups won't have to be backed up.

Differential Partial Database Backup

Differential partial backups use the most recent partial backup as their differential base. They work very similar to a differential backup, except (with some exceptions) they capture only changes made to read/write filegroups .

If you add, drop, or change the status of any of your filegroups, you're best off taking a partial backup prior to resuming differential partial backups. Filegroup changes can have unintended consequences with differential partial backups.

Data Loss:

Differential Partial provide a reduced chance of data loss from a partial backup alone, since the differential partial backups are performed between partial backups.

Size:

A differential partial backup should be relatively small given that it won't backup specified read only filegroups and uses the most recent partial backup as its base.

Resources Required:

The resources required for a differential partial backup will be relatively small compared to full database backups and partial backups.

File and Filegroup Backups

This primarily applies to very large databases (VLDB) with multiple filegroups that have strict availability requirements. In these instances, sometimes it takes too long to perform a full backup, or the size of a full backup is extremely large.

If you add, drop, or change the status of any of your filegroups, you're best off taking a partial backup prior to resuming differential partial backups. Filegroup changes can have unintended consequences with differential partial backups.

Data Loss:

Data loss is minimized with file and filegroup backups through the proper use of transaction log backups. File and Filegroup backups can only be performed under a full recovery model or a bulk logged recovery model.

Size:

The size of individual file or filegroup backups will obviously be smaller than the size required for a full database backup – which is one of the key advantages of using file and filegroup backups in VLDB situations.

Resources Required:

While there is overhead involved in backing up any portion of a VLDB, the resources will likely be much less than the resources required for performing a full database backup on a very large database.

Copy Only Database Backup

Copy Only backups in SQL Server 2005 must be performed using T-SQL and are not logged in the backup and restore sequence for the database. In other words, you can't apply differential backups to this database backup, it is an independent copy separate from the original database. The transaction log is unaffected (will not be truncated) during this type of backup. You might consider making a copy only backup for a special purpose – like database development or testing.

Data Loss:

Not applicable. A copy only database backup is used to make a copy of the database and isn't part of a backup and recovery plan.

Size:

Not Applicable. Not part of the overall backup and recovery plan.

Resources Required:

Resources required for a Copy Only backup would be similar to a full database backup.