This section outlines the backup options available to DBAs and makes recommendations for backing up the databases used by the server configurations. Be aware that these are just recommendations. Any finalized disaster recovery plan must be created by your organization in consultation with its IT infrastructure staff.
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:
Microsoft SQL Server supports the following types of backups:
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.
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.
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.
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.
We recommend that you: