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