For server configuration online backups, it is essential to take full database and transaction log backups.
Microsoft SQL Server supports the following types of backups:
A full database backup contains the full copy of the database as it was at the time when the backup was initiated. Full backups provide a snapshot of the database. Most of the recovery options require a full backup to be available.
A differential database backup records only the data that has changed since the last full database backup. Scheduling frequent differential backups is a good idea because the backups are smaller and they complete quickly. A differential backup without a prior full backup is useless.
A transaction log backup includes all the transactions since the last transaction log backup. Transaction log backups enable recovery up to the last committed transaction.
A file or file group backup consists of backing up individual data files (or the files in the file group). The files in a database can be backed up and restored individually.
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:
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.
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.
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.
Recovering to a point-in-time (for example, a time before unwanted data was entered) requires either full or bulk-logged recovery models.
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 recreated 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: