Specifications

state of the original database when the backup finished, minus any uncommitted

transactions.

For a small database that can be backed up quickly, it is convenient to use only full database

backups. However, as the database becomes larger, full backups take more time to finish

and require more storage space. Therefore, for a large database, you might want to

supplement full database backups with differential backups.

A full backup can form a base for further differential backup or can be used as a standalone

archive.

A differential backup creates an independent file, containing all changes made against the

initial full archive.

A standalone full backup may be an optimal solution if you often roll back the database to

the initial state. In this case, you do not need to re-create the initial full backup, so the

backup time is not crucial, and the restore time will be minimal.

Alternatively, if you are interested in saving the last data state to be able to restore it in case

of a database failure, consider the differential backup. It is particularly effective if your data

changes tend to be small in comparison to the full data volume.

The transaction log records all transactions and database modifications made by each

transaction. The transaction log is a crucial component of the database and, if there is a

system failure, the transaction log might be required to bring your database back to a

consistent state.

If you are backing up filegroups, the transaction logs will be backed up along with the full

backup. Backing up transaction logs prevents data loss after the last backup and lets you

restore the database state to an arbitrary point in time in order to undo the harmful

changes.

To choose the appropriate backup method (or methods), you have to identify the

requirements for the availability of your data in order to choose the appropriate backup

strategy. Your overall backup strategy defines the type and frequency of backups as well as

the type and capacity of the hardware required for the archive location. See

Which Backup

Strategy to Choose?

(p. 29) for our recommendations.

5.1.3 Which Backup Strategy to Choose?

Follow the recommendations below to define the best backup strategy for your organization:

• Database activity is low to medium:

- Full backup once a week

- Differential backup once a day

- Transaction Logs backup every two to four hours

• Database size is small to medium, but activity is high:

- Full backup twice a week

- Differential backup twice a day

- Transaction Logs backup every hour

• Database size is large and activity is high, recovery model is Full or Bulk-Logged:

- Full backup once a week

- Differential backup once a day