Restoring the Database

I hope you have turned to this chapter out of interest, rather than necessity. If you are reading this because you have just lost a database, don't despair. The restore features of SQL Server are as robust and easy to use as the backup features covered in the last section. You did read the backup section, didn't you?

Obtaining Information About Your Backups

Before restoring from a backup device, you should ensure that it is valid and that it contains the correct backups. This information can be obtained in Enterprise Manager by double-clicking a backup device and selecting View Contents. Alternately, you can use the following Transact -SQL RESTORE options:

• RESTORE HEADER ONLY . This returns the header information of a specified backup file or backup set. This header information includes the name and description, the type of media, the backup method, the date and time, the size of the backup, and the sequence number of the backup.

• RESTORE FILELISTONLY . This returns information about the original database or transaction log files that are in a backup file.

• RESTORE LABELONLY . Use this command to return the label information from the backup media.

• RESTORE VERIFYONLY . This command verifies that all files that make up a backup set are complete and that all backups are readable.

Restoring Databases with T-SQL

The T-SQL command RESTORE DATABASE is used not only to restore a damaged database, but also to move data and log files, restore a copy of a database with a different name, restore a file or file group , do a partial restore, and to initiate a standby server. Listing 16.9 shows the RESTORE DATABASE syntax.

Listing 16.9 The Syntax for RESTORE DATABASE

 RESTORE DATABASE {  database_name   @database_name_var  }     [< file_or_filegroup > [ ,...  n  ]] [ FROM < backup_device > [ ,...  n  ] ] [ WITH     [PARTIAL]     [ RESTRICTED_USER ]     [ [ , ] FILE = {  file_number   @file_number  } ]     [ [ , ] PASSWORD = {  password   @password_variable  } ]     [ [ , ] MEDIANAME = {  media_name   @media_name_variable  } ]     [ [ , ] MEDIAPASSWORD = {  mediapassword   @mediapassword_variable  } ]     [ [ , ] MOVE '  logical_file_name  ' TO '  operating_system_file_name  ' ]             [ ,...  n  ]     [ [ , ] KEEP_REPLICATION ]     [ [ , ] { NORECOVERY  RECOVERY  STANDBY =  undo_file_name  } ]     [ [ , ] { NOREWIND  REWIND } ]     [ [ , ] { NOUNLOAD  UNLOAD } ]     [ [ , ] REPLACE ]     [ [ , ] RESTART ]     [ [ , ] STATS [ =  percentage  ] ] ] 

The [< file_or_filegroup > [ ,... n ]] clause is, as the brackets imply, optional. By specifying a file, filegroup, or a list of files or filegroups, you are instructing SQL Server to restore only those files or filegroups. This allows you to restore only the damaged part of the database, which could vastly decrease your restore time. For more information, see the subsequent section on file and filegroup restore.

The PARTIAL option is new to SQL Server 2000. The partial clause is specified in conjunction with a restore from a full backup, a file or filegroup clause, and usually a MOVE option. Optionally, you can choose to restore additional differential and or log backups. A partial restore differs from file or filegroup restore in that it restores the primary filegroup and the filegroup specified (if a file is specified, all files in its filegroup are restored). All other filegroups are marked unavailable. For more information, see the subsequent section on partial restore.

The RESTRICTED_USER restricts access to the restored database to members of the sysadmin, db_owner, and dbcreator roles. It replaces the DBO_ONLY option.

The FILE option specifies which backup set to restore from the backup device. A value of 3, for example would restore the third backup set on the device.

The PASSWORD , MEDIANAME , MEDIAPASSWORD , NOREWIND REWIND , NOUNLOAD UNLOAD , RESTART , and STATS are identical to those in the BACKUP command.

The MOVE option allows you to restore a file to a new physical location. This is handy when moving a database or log file to a new disk. It is also invaluable, if you lose a disk and must restore the database as quickly as possible; restore the database to an existing disk, and when time permits , replace the defective disk and move the database back to the proper location.

If you have a published database (see the chapter on replication) that is also being backed up to a warm standby server, the KEEP_REPLICATION option will preserve your replication settings when you restore to the standby server.

The REPLACE option allows you to restore over an existing database, even if the structure of the database is different than that contained in the backup. SQL Server performs a safety check before a restore, which ensures that it doesn't replace a database if the database exists and the name is different than the name in the backup set, or if the set of files in the database differs from that recorded in the backup set. The REPLACE option cancels this safety check.

NOTE

Conspicuous by its absence here is the syntax for a differential restore. That is because there is none. To perform a differential restore, specify the location of a differential backup in the FROM clause; SQL Server will recognize it as such and act accordingly .

Restoring Transaction Logs with T-SQL

The RESTORE LOG command is used to recover your transaction log backups. More correctly, after you restore your database backup you apply the associated log backups. The transaction log backups are a continuous record of all the transactions that have transpired on your database. The log records are written serially , so all transaction log backups must be applied in order. You can't restore log1, log2, and log4 because log4 might try to update a record that was created in log3. You can see how this would be a problem. If you have a missing log backup, you can only restore as far as the log backup prior to it. In this case, only log1 and log2 could be recovered.

Listing 16.10 shows the RESTORE LOG syntax. For clarity, I have omitted the options common with the database backup and restore commands.

Listing 16.10 The RESTORE LOG Syntax

 RESTORE LOG {  database_name   @database_name_var  } [ FROM < backup_device > [ ,...  n  ] ] [ WITH         [ [ , ] { NORECOVERY  RECOVERY  STANDBY =  undo_file_name  } ]         [ [ , ] STOPAT = {  date_time   @date_time_var  }          [ , ] STOPATMARK = '  mark_name  ' [ AFTER  datetime  ]          [ , ] STOPBEFOREMARK = '  mark_name  ' [ AFTER  datetime  ]     ] ] 

The NORECOVERY RECOVERY STANDBY options specify which state to leave the database in after the log is applied. If NORECOVERY is specified SQL Server does not roll back any uncommitted transactions in the log, and it leaves the database in recovery mode. Use this if you still have more logs to apply. The RECOVERY option rolls back any uncommitted transactions and opens the database for use. Use this option only after the last log has been applied. RECOVERY is the default. The STANDBY option is similar to NORECOVERY , but it also leaves the database in read only mode. An undo file must be specified when using STANDBY . See the section "Using a Standby Server" later in this chapter for more information.

The STOPAT option accepts a date and time input to stop recovery at that point in time. New for SQL Server 2000 are STOPATMARK and STOPBEFOREMARK . These options allow recovery to a named 'mark' within a transaction. See the section "Restoring to a Point in Time" later in this chapter for further information.