The backup and restore component of Microsoft® SQL Server™ provides an important safeguard for protecting critical data stored in SQL Server databases. Backing up and restoring a database allows for the complete restoration of data over a wide range of potential system problems:
If one or more of the disk drives holding a database fail, you are faced with a complete loss of data unless you can restore an earlier copy of the data.
If a user or application either unintentionally or maliciously makes a large number of invalid modifications to data, the best way to deal with the problem may be to restore the data to a point in time before the modifications were made.
If a server is disabled permanently, or a site is lost to a natural disaster, you may need to activate a warm standby server or restore a copy of a database to another server.
Additionally, backing up and restoring databases is useful for nonsystem problems, such as moving or copying a database from one server to another. By backing up a database from one computer, and restoring the database to another, a copy of a database can be made quickly and easily.
Backing up a database makes a copy of a database, which can be used to restore the database if it is lost. Backing up a database copies everything in the database, including any needed portions of the transaction log.
The transaction log is a serial record of all the modifications that have occurred in a database, and which transaction performed each modification. The transaction log is used during recovery operations to roll forward completed transactions, and roll back (undo) uncompleted transactions. For more information, see Transaction Logs.
Backing up a transaction log backs up only the changes that have occurred in the transaction log since the transaction log was last backed up.
A backup operates like a fuzzy snapshot taken of a database or transaction log:
Restoring a database backup returns the database to the same state it was in when the backup was created. Any incomplete transactions in the database backup, (transactions that were not complete when the backup operation completed originally), are rolled back to ensure the database remains consistent.
Restoring a transaction log backup reapplies all completed transactions that are in the transaction log to the database. When applying a transaction log backup, SQL Server reads forward through the transaction log, rolling forward all the transactions on the transaction log. When SQL Server reaches the end of the transaction log, it has re-created the exact state of the database at the time the backup operation started. The restore operation then rolls back all transactions that were incomplete when the backup operation started.
Note Backing up a database does not back up full-text index data in full-text catalogs. However, if full-text indexes have been defined for tables, the metadata for the full-text index definitions are stored in the system tables in the database containing the full-text indexes. Therefore, the metadata for the full-text indexes are backed up when a database backup is created. After a database backup is restored, the full-text index catalogs can be re-created and repopulated. For more information, see Full-text Indexes.
| Backup/Restore Architecture | Databases |
| Copying Databases to Other Servers | Transactions |