The backup and restore components of Microsoft® SQL Server™ provide the capability of creating a copy of a database. This copy is stored in a location that is protected from failures of the server running SQL Server. If the server running SQL Server fails, or if the database is somehow damaged, the backup copy can be used to re-create, or restore, the database.
SQL Server has sophisticated backup and restore capabilities:
This allows backup and restore processes to be tailored to how critical the data in the database is. Noncritical databases that can be easily re-created from some other source may have no backups, other databases may have simple backups that can re-create the database to the night before a failure, and critical databases may have sophisticated backups that will restore the database right up to the point of failure.
Users can execute these statements directly from applications or Transact-SQL scripts, stored procedures, and triggers. It is more common, however, to use the SQL Server Enterprise Manager interface to define a backup schedule and let SQL Server Agent run the backups automatically according to the schedule. The Database Maintenance Wizard can be used to define and schedule a full set of backups for each database. This fully automates the backup process such that it proceeds with minimal or no need for operator action.
These record the backups that are made for each database. If a database has to be restored, the SQL Server Enterprise Manager Restore Database dialog box presents the user with a list of all the backups available for the database. The Restore Database dialog box also has logic to display which set of the backups in the history can be used to restore the database in the shortest possible time. When the dialog box is displayed, the backups needed to restore the database are checked. If the user knows that one of the backups is not available, for example if a tape cartridge was damaged or lost, they can deselect that backup and SQL Server Enterprise Manager calculates a new restore process. When the user agrees with the restore process, SQL Server Enterprise Manager restores the database, prompting for tapes as needed.
The backup processing and internal data structures of SQL Server version 7.0 have been improved so that backups maximize their rate of data transfer with minimal effect on transaction throughput.
The new data structures in SQL Server 7.0 databases and new backup and restore algorithms significantly speed the rate at which backup and restore operations can transfer data. SQL Server backup and restore operations can also run in parallel against multiple backup files or tape drives. This further improves the backup and restore data transfer rates.
This eliminates the need to execute a separate CREATE DATABASE or CREATE DATABASE FOR LOAD statement if the database does not exist at the time the RESTORE statement is executed.
Backup and restore processes should be planned together. The administrators must first determine the criticality of the data in the database. They must determine if it is acceptable to just restore the database to a point such as the night before the failure, or if the database must be restored to a point as close as possible to the time of failure. They must also determine how long the database can be unavailable, whether it must be brought back online as quickly as possible, or if it does not need to be restored immediately.
After the restore requirements are determined, the administrators can then plan a backup process that maintains a set of backups that will meet the restore requirements. The administrators can choose the backup processes that can be performed with the minimum effect on the system as it runs, yet still meet the restore requirements.
Backing Up and Restoring Databases