The following questions are intended to
This chapter will teach you how to restore
To complete the lessons in this chapter, you must
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.
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
nameof 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
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:
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
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
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.
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.
The recovery process can
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
When you restore a database, SQL Server automatically
SQL Server performs a safety check when you execute the RESTORE DATABASE statement. This internal mechanism
SQL Server does not restore the database in the following situations:
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.
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.
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.
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.