Backing up Microsoft SQL Server databases

This section outlines the backup options available to DBAs and makes recommendations for backing up the databases used by the server configurations.

In this topic:

Microsoft SQL Server backup overview

An application backup consists of backing up both the database and the application archive files and you have a choice between online and offline backups. If all of your archive files are in Native-II format, you can back up a server configuration online, without shutting it down or locking it. If you have a pre-2005 StarTeam Server release, you must convert older archives from Native-I to Native-II format prior to performing an online backup. Regardless of the StarTeam Server release, you must also set up your database for an online backup.

For server configuration online backups, it is essential to take full database and transaction log backups. The entire database can be recreated from a database backup in one step by restoring the database. The restore process overwrites the existing database or creates the database if it does not exist. The restored database will match the state of the database at the time the backup completed, minus any uncommitted transactions. Uncommitted transactions are rolled back when the database is recovered.

Based on the resource requirements, the DBA can also choose the recovery model for the database. The recovery model balances logging overhead against the criticality of fully recovering the data. The recovery models supported by Microsoft SQL Server are:

Full	The data is critical and must be recoverable to the point of failure. All data modifications are logged. All Microsoft SQL Server recovery options are available.
Bulk-logged	Certain bulk operations, such as bulk copy operations, SELECT INTO, and text processing, can be replayed if necessary, so these operations are not fully logged. You can recover only to the end the last database or log backup.
Simple	All data modifications made since the last backup are not available. This type of recovery scenario has the lowest logging overhead, but cannot recover past the end of the last backup.

Back to top

Full database backup

A full database backup creates a duplicate of the data that is in the database. This is a single operation, usually scheduled at regular intervals. Full database backups are self-contained. Full backups provide a snapshot of the database. Most of the recovery options require a full backup to be present.

We strongly recommend the use of full backups.

Back to top

Differential backup

A differential database backup records only the data that has changed since the last database backup. Frequent differential backups are recommended to reduce backup times. Making frequent backups decreases the risk of losing data.

Differential backups restore the data that they contain to the database. Differential backups cannot be used to recover the database to a point in time.

The availability of a differential backup minimizes the time it takes to roll forward transaction log backups when restoring a database.

Back to top

Transaction log backup

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time or to the point of failure.

When restoring a transaction log backup, Microsoft SQL Server rolls forward all the changes recorded in the transaction log. When Microsoft SQL Server reaches the end of the transaction log, it has re-created the exact state of the database at the time of the backup operation.

If the database is recovered, Microsoft SQL Server then rolls back all transactions that were incomplete when the backup operation started. Transaction log backups generally use fewer resources than database backups. As a result, you can create them more frequently than database backups. Frequent backups decrease the risk of losing data. For high volume Online Transaction Processing (OLTP) environments, it is desirable to create transaction log backups more frequently.

Transaction log backups can only be used with Full and bulk-logged recovery models. The transaction log cannot be backed up during a full database backup or a differential database backup. However, the transaction log can be backed up while a file backup is running.

Never backup a transaction log before a database backup is created because the transaction log contains the changes made to the database after the last backup was created.

Never truncate the transaction log manually because it breaks the backup chain. If a transaction log has been truncated, take a full database backup to start a new backup chain.

Back to top

File backups

A file or file group backup consists of the backing up of individual data files (or the files in the file group). A file-based recovery model increases the speed of recovery by allowing you to restore only the damaged files without restoring the rest of the database. For example, suppose a database is comprised of several files located physically on different disks and one disk fails. Only the file on the failed disk needs to be restored and rebuilt using the transaction log backup.

File backup and restore operations must be used in conjunction with transaction log backups. For this reason, file backups can only be used with the full recovery and bulk-logged recovery models.

Back to top

Recommendations

We recommend that you:

• Use the full recovery model.
• Perform a full database backup once every day. For full database sizes greater than 3 GB, it is okay to perform full backups on alternate days. If you perform full backups on alternate days, we strongly recommend that you create daily differential backups.
• Create daily transaction log backups after the completion of the full or differential backup. In addition to this, schedule a transaction log backup every 4 hours. Never truncate a transaction log manually.
• In case of a disaster, create a backup of the currently active transaction log. If active transaction log backup is not available (for example, because a media failure for the drive hosting the transaction logs and drive is not being mirrored), the database cannot be recovered past the last available transaction log backup. This would hamper a point-in-time recovery beyond the last available transaction log backup.
• Label the backup media correctly.
• Keep backup copies in offsite locations.

Back to top