SQL Server 2000 Database Backup Types

Database, Differential, Transaction Log and File Backups

There are four main types of backups in SQL Server 2000: Database, Differential, Transaction Log, and File.

DATABASE

With this backup you are backing up the entire database and will be able to restore the entire database from just this backup. This backup will match the state of the database at the time the backup completes (except for uncommitted transactions which on restore of the backup will be rolled back). Generally this backup takes up the most time and uses up the most space out of all of the different types of backups.

DIFFERENTIAL

A differential backup will record all of the data that has changed since the last database backup. You must have a database backup in place to use a starting point for your differential backup. Like database backups, differential backups will match the state of the database at the time that the backup is completed. Differential backups aren't like transaction log backups because they aren't incremental. When a differential backup occurs, it does not pay any regard to any differential backups that occurred before, it looks back only to the last database backup and records any data changed since that database backup.

TRANSACTION LOG

Transaction log backups record all transactions that have been recorded against the database since the last transaction log backup. You must have either a differential database backup or a database backup to use as a starting point for your transaction log backups. Unlike database and differential backups, transaction log backups will match the state of the database at the time that the backup is started. These backups use less space and time than database backups, and should be taken frequently to avoid loss of data. Your transaction logs should also be located on a separate disk if possible from your data files.

FILE

File backups involve backing up individual files within a database. This can be the quickest way to restore, but it also has a lot of overhead associated with it. You must keep track of your file backups as well as use these file backups in conjunction with transaction log backups. Transaction log backups must be performed after a file backup is completed. You will restore your file backups first, and then all transaction log backups that occurred.

EXAMPLE

Let's say that a full backup occurs Sunday at 8AM. Transaction Log backups occur every day at 10AM and 4PM, and Differential backups occur every day at 6PM. In our scenario below, a failure occurs at noon on Wednesday. There are various ways to restore the database up to the point at which the 10AM transaction log backup started on Wednesday.

Day	Time	FULL	Differential	Transaction Log
SUN	8AM	F1
	10AM			T1
	4PM			T2
	6PM		DIFF1
MON	10AM			T3
	4PM			T4
	6PM		DIFF2
TUES	10AM			T5
	4PM			T6
	6PM		DIFF3
WED	10AM			T7
FAILURE OCCURS	12PM

For all scenarios, the full backup must be restored first. Both the differential and transaction log backups both require the full backup to be restored first before they can be restored.

Quickest Restore

The quickest way to restore would be to restore: the full backup, followed by the most recent differential backup, followed by all transaction logs up to the most recent. That would result in the following restore: F1, DIFF3, T7. You need to restore F1 as mentioned earlier. Then you would restore DIFF3. DIFF3 contains all changes since the last full backup. Then you would restore all transaction log backups that occurred after the last differential backup, which in our case is only T7.

Complication - Corrupt Backup File

Lets say that DIFF3 has somehow become corrupted and we can't use it for restore. In this case, we would restore F1, then DIFF2, then apply T5, T6, and T7.

Another Way

You could also just apply the full backup followed by all transaction logs that have occurred since the full backup was taken (F1, T1, T2, T3, T4, T5, T6, T7)