The backup and restore components of Microsoft® SQL Server™ 2000 allow you to create a copy of a database. This copy is stored in a location protected from the potential failures of the server running the instance of SQL Server. If the server running the instance of 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 2000 provides these sophisticated backup and restore capabilities:
These options allow 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 the BACKUP and RESTORE statements directly from applications, Transact-SQL scripts, stored procedures, and triggers. It is more common, however, to use SQL Server Enterprise Manager to define a backup schedule, and then let SQL Server Agent run the backups automatically according to the schedule. The Database Maintenance Plan Wizard can be used to define and schedule a full set of backups for each database. This fully automates the backup process, requiring minimal or no operator action.
The backup history tables record the backups for each database. If a database has to be restored, the Restore Database dialog box in SQL Server Enterprise Manager 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 a user knows that one of the backups is not available (for example, if a tape cartridge was damaged or lost), the user 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 2000 are structured so that backups maximize their rate of data transfer with minimal effect on transaction throughput.
The data structures in SQL Server 2000 databases and the backup and restore algorithms support high data transfer rates for backup and restore operations. SQL Server backup and restore operations can also run in parallel against multiple backup files or tape drives, which further increases 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. Based on the resource requirements, the administrators 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 are:
The data is critical and must be recoverable to the point of failure. All data modifications are logged. All SQL Server 2000 recovery options are available.
Certain bulk operations (bulk copy operations, SELECT INTO, text processing) can be replayed if necessary, so these operations are not fully logged. Can only recover to the end of the last database or log backup.
All data modifications made since last backup are expendable, or can be redone. Lowest logging overhead, but cannot recover past the end of the last backup.