| |
 |
Assume
that over a period of time, a database accumulates data as
follows:
Full database backup
As the name implies, a full database backup contains the entire
database. It contains the data files of a database, and the active
portion of the transaction log as at the end of the backup.
The basic syntax for a full database backup is as follows:
| BACKUP DATABASE AdventureWorks TO DISK =
'g:\backups\AdventureWorks.bak' |
Differential database backup
A differential database backup contains the changed data pages
since the last full database backup, and the active portion of the
transaction log as at the end of the backup.
The syntax to perform a differential backup is similar to that for
a full database backup, with the exception of the DIFFERENTIAL
option e.g.
| BACKUP DATABASE AdventureWorks TO DISK =
'g:\backups\AdventureWorks.bak' WITH
DIFFERENTIAL |
Transaction log backup
A transaction log backup backs up the transaction log records that
have not yet been backed up, up to the last log record that exists
at the time the backup ends. You can only perform a transaction log
backup if your database is running the full or bulk-logged recovery models.
A common misconception is that a full database backup will truncate
the transaction log, so that transactions that were backed up will
no longer be backed up again in the next transaction log backup. A
full database backup does not truncate the transaction log.
The syntax to perform a transaction log backup is similar to that
for a full database backup, with the exception of the second
keyword i.e. instead of DATABASE, it's LOG e.g.
| BACKUP LOG AdventureWorks TO DISK =
'g:\backups\AdventureWorks.bak' |
So which types of backups should I
perform?
As usual, the answer is it depends. It depends on your disaster
recovery requirements, and is usually a compromise between the
amount of data loss that's acceptable, the impact of backups on
normal database operations, and the time required to recover from a
disaster.
If your environment can only tolerate a loss of 5 minutes worth of
data, you would obviously need to perform some kind of backup every
5 minutes. For a sizeable database, a full database backup every 5
minutes would impact the regular operations of a database too much
to be of any use. Similarly for a differential backup, if the
database has undergone a lot of changes since the last full backup.
A transaction log backup would be the best fit, as it only backs up
transactions that have been created since the last transaction log
backup.
However, to restore a database using only transaction log backups,
you will need to restore the entire chain of backups from the
initial recovery point of the database up to the latest transaction
log. This in itself may be too long, if the initial recovery point
is some time back.
You can use differential backups to bring forward the initial
recovery point from which transaction log backups should start.
However, for an active database, a differential backup takes longer
than a transaction log backup, impacts database operations more,
and consumes more space.
These are some of the points that you should consider when
formulating your backup plan:
|
|
·
|
how
much data loss is acceptable
|
|
|
·
|
will
the backups impact regular database operations
|
|
|
·
|
is
there a maintenance window whereby high impact backup operations
can be performed
|
|
|
·
|
what
is the acceptable time to recovery
|
|
|
·
|
is
there a need for the ability to restore to a point in time, in
which case transaction log backups will be required
|
|
|
·
|
is
there adequate storage space to store all the required backups
|
|
|
·
|
can
high availability solutions be used to complement the disaster
recovery plan e.g. clustering, log
shipping, database mirroring in SQL Server 2005 and above etc
|
Document history
| 7/16/2008 | Initial release. |
|
