Restore vs. Recovery
Database recovery and database restore are similar, but they aren't the same. A complete restore almost always includes a recovery operation, but recovery doesn't have to include a restore. Restore is a manual process of loading backed-up data that an administrator must initiate. Recovery is an automatic process that can occur at the end of a restore operation and occurs every time an administrator restarts SQL Server. Although recovery is usually part of a restore operation, the administrator controls the process, as I discuss later. (For information about a difference between the recovery process of SQL Server 2000 and 7.0, see the sidebar "Recovery in SQL Server 2000 vs. 7.0," page 42.)
During database recovery (either at the end of a restore operation or after a restart), SQL Server compares the records of transactions in the transaction log with the data in the database. Recovery performs both redo (roll-forward) and undo (rollback) operations. In a redo operation, SQL Server examines the log and verifies that each change is already in the database. If a committed transaction is recorded in the transaction log but doesn't exist in the data pages, SQL Server redoes the transaction. After a redo, SQL Server guarantees that every change that the transaction made has been applied. If parts of a transaction appear in the database but that transaction was never committed, SQL Server performs an undo operation to remove the changes that the uncommitted transaction made. After the entire recovery process is finished, the database and the transaction log are synchronized. All completed transactions recorded in the log appear in the database, and no part of an uncompleted transaction appears in the database.
Restoring a Damaged Database
If you need to restore a database because of a media failure or because of an error that destroyed or changed data, you can restore the database to the original SQL Server. To restore a database, you start by restoring the most recent full backup. If you're restoring the database because a disk is damaged, you need to restore the database to a new (undamaged) location. By default, a SQL Server restore operation creates the database if it doesn't already exist and uses the same file locations for the data and log files that the original database used. To verify those file locations, you can run the following command against the disk or tape backup file:
RESTORE FILELISTONLY FROM <backup device location>
To create the restored database in a different location on the same SQL Server, you must use the RESTORE DATABASE command's MOVE option. (BOL contains the complete syntax for the MOVE option.) If your physical media hasn't been damaged and you're restoring only to correct user errors, you might want to restore a backed-up copy of your database to the same location on disk—right on top of your current database.
Note that no users can be in the database when you perform this operation. In SQL Server 2000, you can use the ALTER DATABASE command to change the database status to single-user mode and disconnect users from the database. (BOL contains complete documentation for ALTER DATABASE.) However, SQL Server 7.0 has no automatic way to disconnect users from a database. You can run a script that checks the sysprocesses table for connected users, issue a KILL command for each, then try to change the database to single-user mode—but you can't keep new users from logging in during the process. To try to prevent logins, you can use the OS service manager to put the server in a pause state, but this pause affects more databases than just the one you want to restore. Also remember that using Enterprise Manager to inspect your database or perform the restore uses up a connection to the database, so if you're in single-user mode, you can't simultaneously use Query Analyzer to connect to the database.
Restoring and Renaming a Database
If you want to restore a database and give it a new name, perhaps because you want an identical copy of a database on the same server, you might need to use the REPLACE option with the RESTORE DATABASE command. The REPLACE option is necessary when the database name in the backup files doesn't match the name of the database you're specifying in the RESTORE DATABASE command and the name you're specifying in the RESTORE command already exists on the SQL Server. The REPLACE option helps prevent you from accidentally overwriting one database with the backup of a different database. For example, if you have a backup of the Northwind database in a file and you try to restore the backup into the Pubs database , SQL Server won't let you perform the restore without the REPLACE option. However, if you do specify REPLACE, the backed-up data from Northwind will overwrite the original Pubs data.
You also need the REPLACE option when you use the MOVE option and specify an existing file. The MOVE option lets you use the RESTORE command to recreate a database in a different physical location, perhaps on a new, faster disk. However, if the file specified as the destination of the MOVE option already exists, SQL Server assumes that the existing file belongs to a different database. Usually, RESTORE DATABASE refuses to overwrite existing files, but REPLACE lets RESTORE DATABASE overwrite an existing file. You need to be aware of one caveat when you use the REPLACE option: Usually, the user who is running the RESTORE DATABASE command has to be only the database owner (DBO). But when you're using REPLACE, SQL Server behaves as if you're creating a new database. The user who is running the RESTORE DATABASE command with the REPLACE option must have permission to create a database or be in a group that has such permissions. In my next article, I'll discuss a few other caveats that also apply to a nonadministrator DBO running a restore operation with REPLACE.
Prev. page
1
[2]
3
4
next page 
