Lesson 1: The SQL Server Recovery and Restore Processes

[Previous] [Next]

SQL Server has both a manual and automatic recovery and restore process. The automatic recovery process occurs when you restart SQL Server after a database failure or shutdown. It attempts to ensure the consistency of data. Manual recovery is necessary after restoring a database, in order to put the database in a consistent state by recovering the restored transaction log.

IMPORTANT
Run the Trans.avi file from the \AVIs folder on the Supplemental Course Materials CD-ROM, by selecting Run from the Start menu and typing D:\AVIs\Trans.avi (where D: is the name of your CD-ROM drive).

After this lesson, you will be able to

Describe the SQL Server recovery process
Describe the activities that take place during a SQL Server restore

Estimated lesson time: 15 minutes

Database Recovery

During SQL Server operation, a database exists in two places: most of the data pages are on disk in the primary and secondary data files, and some pages are in memory, in the data cache. All database modifications are recorded in the transaction log as they occur, as part of a transaction. Once a modification has been recorded in the transaction log, the pages in the data cache are modified. The following details regarding transactions are pertinent to the recovery process:

If a transaction is busy modifying pages in the data cache (an uncommitted transaction), the pages being modified reflect an inconsistent state in the database. Pages modified by a transaction are not written to disk until after the transaction is complete (committed), thus guaranteeing the consistency of the data file pages. If the transaction is canceled (rolled back), the changes to the pages in the data cache will be undone.

If the transaction that modified pages in the data cache is complete (a committed transaction), the pages are part of the consistent state of the database, and they will be written to disk by the next checkpoint process. The checkpoint process will then mark the transaction in the transaction log to indicate that it has been applied to the data file pages.

When the server stops, expectedly or unexpectedly, there may be committed transactions in the transaction log that have not yet been checkpointed. There may also be uncommitted transactions in the transaction log; these transactions can never be committed, as the server has stopped. The recovery process deals with these committed and uncommitted transactions that occurred after the last checkpoint.

The SQL Server recovery process is an internal mechanism that ensures that your database is consistent by examining the transaction log and taking appropriate actions. The recovery process runs automatically when SQL Server is started and can be initiated manually during restore operations. The process is as follows:

SQL Server examines the transaction log, beginning at the last checkpoint. A checkpoint is like a bookmark, marking the point up to which all data changes have been written to the database.

If committed transactions are found, they have not yet been written to the database (otherwise, they would not be after the last checkpoint). SQL Server rolls these transactions forward, applying their changes to the database.

If the transaction log contains any uncommitted transactions, SQL Server rolls them back (removes them). The fact that the recovery process is taking place means that these uncommitted transactions can never be committed, so they must not be written to the database.

NOTE
Although it may seem that changes are not seen by connected users until after a checkpoint takes place, this is not the case. The changes are available in the data cache immediately after a transaction is committed. Connected users always get data directly from the data cache, so they will see the committed changes even if they have not yet been written to disk.

Automatic Recovery

When your system is restarted after a failure or shutdown, SQL Server begins the automatic recovery process to ensure data consistency. You do not have to start this process manually—it occurs automatically.

Manual Recovery

The recovery process can optionally be initiated as part of the restore process. The manual recovery process is similar to the automatic recovery process that occurs when SQL Server is restarted.

Manual recovery is necessary after restoring a database, in order to put the database in a consistent state by recovering the restored transaction log. Manual recovery must be performed only once when restoring a database. If you have transaction log backups to restore as well as the database backup, perform the manual recovery after restoring the database backup and all of the transaction log backups. If you are restoring only a database backup, perform the manual recovery after the database restore; this is necessary because a database backup includes a copy of the transaction log at the time of the database backup.

SQL Server Activities During the Restore Process

When you restore a database, SQL Server automatically performs certain actions to ensure that your database is restored quickly and with minimal impact on production activities.

Safety Check

SQL Server performs a safety check when you execute the RESTORE DATABASE statement. This internal mechanism prevents you from accidentally overwriting an existing database with a backup of a different database or with incomplete information.

SQL Server does not restore the database in the following situations:

If you are trying to restore a database using a new name, and a database with that name already exists on the server.

If the set of database files on the server is different from the set of database files contained in the backup set.

If not all of the backup devices needed to restore a database or filegroup are supplied. SQL Server generates an error message specifying which backup devices must be restored as a unit (in one restore operation).

For example, if you attempt to restore a backup of the Northwind database to a database named Accounting, and Accounting already exists on the server, SQL Server will prevent the restore from occurring. If you intend to restore a backup of Northwind and overwrite the data in Accounting, you can override the safety check with the REPLACE option of the RESTORE statement.

Database Re-creation

When you restore a database from a complete database backup, SQL Server re-creates the original database files and places them in the locations that were recorded when the backup was made. All database objects are re-created automatically. You do not need to rebuild the database schema before you restore the database.

NOTE
In previous versions of SQL Server, it was necessary to re-create a device and a database before restoring from backup. This is not necessary with SQL Server 7.