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.
Page 3: Database Recovery Models
»
See All Articles by Columnist Michael Aubert
» 
Comment and Contribute
