SQL Server provides a backup tool that you
can start through SQL Server's Enterprise Manager
using the Backup
Database option for a database. You can
also start this through Transact-SQL command
BACKUP in
the Query Analyzer. SQL Server supports four
different backup types, including:
NOTE Other
database applications have similar backup types.
Complete
database backup
A complete
database backup copies all data pages
and transaction log pages of a selected database
on a backup set. It is the same as the normal
backup for file systems.
Advantages:
Easy to restore. Since the backup set contains
all information needed to reconstruct a
database, you only need to restore from
the latest set.
Disadvantages:
Takes the longest time and most storage
resources for each backup.
Example:
Perform a complete database backup as the
first backup set, or if the database is
small, or after a major change in the database.
Differential
backup
A differential
backup copies all pages of a selected
database that have been modified since the last
complete backup. It is similar to the differential
backup for file systems.
Advantages:
Since the backup set consists only of pages
modified since the last complete backup,
a performing a differential backup creates
a relatively small backup set and requires
a short time to perform the backup.
Disadvantages:
Creates a larger backup set compared to
transaction log backups.
Example:
Make a complete backup every Sunday; make
a differential backup on all the other days.
Transaction
log backup
A transaction
log backup copies only the transaction
log file since the last complete or transaction
log backup. It is similar to the incremental
backup for file systems.
Advantages: Since
the backup set consists only of new and
updated transactions since the last complete
or log backup, performing a transaction
log backup creates the smallest backup set
and requires the shortest time to perform
the backup.
Disadvantages:
When restoring transaction logs, you must
use the sets that contain all transaction
log backups after a complete or differential
backup. Using the SQL Enterprise Manager
to restore the transaction log backup on
the same server automatically selects all
the necessary sets.
Example:
Make a complete backup every Sunday; back
up the log every 4 hours, except during
a complete backup.
File
or file group backup
A file or file group backup
backs up individual files or file groups within
a database. This can help when backing up very
large databases when you don't have enough time
to perform backups.
Advantages:
Allows you to selectively back up individual
parts of a database.
Disadvantages:
Requires careful planning to ensure you
can recover the database. For example, if
any tables or indexes span multiple file
groups, you must back up these file groups
together.
Example:
Make a backup for file group 1 on
Mon, Weds, Fri; make a backup for file group
2 on Tues, Thurs, Sat; make a log backup
after each file group backup.