SQL Server 2000 Administration in 15 Minutes a Week: Database Backups - Page 2

June 14, 2002

Database Backup Types

SQL Server has three main backup types: Full, Differential, and Log. In addition to the main three that backup the whole database, there are also a few additional types of backups that can be used to backup a single file or a filegroup. 

Full Backups - a full backup copies all the extents from the database. If you need to restore your database and are using full backups, you only need to apply the last full backup. However, full backups are the slowest of all the backup types.

Differential Backups - a differential backup only backs up the extents that have changed from the last full backup. If you need to restore your database and are using differential backups, you will need to apply the last full backup and then the last differential backup you have made. Differential backups are faster to create, but they take longer to restore than a full backup because you have to apply the full backup and then the differential backup.

Log Backups - a log backup is used to backup the transaction log from the last full or transaction log backup. You may or may not be able to make log backups -- it depends on the  recovery model you are using. If you need to restore your database and you are using full and log backups, you would need to restore the last full backup and all (in order) the transaction log backups.

One thing to note about backups is that they are done with the database online. This is called a "fuzzy backup" because the backup is not from a single point in time. The backup copies extents from the database and if any modifications are made, the backup just continues copying. To keep consistency, full and differential backups capture the portion of the log file from the point the backup started to the end of the backup.

SQL Server can backup to a file on your hard drive, a file on a network drive, a tape drive, or a named pipe.