All restore operations can be executed using two Transact-SQL statements:
- RESTORE DATABASE
- RESTORE LOG
The RESTORE DATABASE statement is used to perform the restore process for a database. The general syntax of this statement is
RESTORE DATABASE {db_name | @variable}
[FROM device_list]
[WITH option_list]
db_name is the name of the database that will be restored. (The name of the database can be supplied using a variable, @variable.) device_list specifies one or more names of devices on which the database backup is stored. (If you do not specify the FROM clause, only the process of automatic recovery takes place, not the restore of a backup, and you must specify either the RECOVERY, NORECOVERY, or STANDBY option. This action can take place if you want to switch over to a standby server.) device_list can be a list of names of disk files or tapes. option_list comprises several options that can be specified for the different backup forms. The most important options are
RECOVERY/NORECOVERY/STANDBY
CHECKSUM/NO_CHECKSUM
REPLACE
PARTIAL
STOPAT
STOPATMARK
STOPBEFOREMARK
The RECOVERY option instructs the Database Engine to roll forward any committed transaction and to roll back any uncommitted transaction. After the RECOVERY option is applied, the database is in a consistent state and is ready for use. This option is the default.
Note – Use the RECOVERY option either with the last transaction log to be restored or to restore with a full database backup without subsequent transaction log backups.
With the NORECOVERY option, the Database Engine does not roll back uncommitted transactions because you will be applying further backups. After the NORECOVERY option is applied, the database is unavailable for use.
Note – Use the NORECOVERY option with all but the last transaction log to be restored.
The STANDBY option is an alternative to the RECOVERY and NORECOVERY options and is used with the standby server. (The standby server is discussed later, in the section “Using a Standby Server.”) In order to access data stored on the standby server, you usually recover the database after a transaction log is restored. On the other hand, if you recover the database on the standby server, you cannot apply additional logs from the production server for the restore process. In that case, you use the STANDBY option to allow users read access to the standby server. Additionally, you allow the system to restore additional transaction logs. The STANDBY option implies the existence of the undo file that is used to roll back changes when additional logs are restored.
The CHECKSUM option initiates the verification of both the backup checksums and page checksums, if present. If checksums are absent, RESTORE proceeds without verification. The NO_CHECKSUM option explicitly disables the validation of checksums by the restore operation.
The REPLACE option replaces an existing database with data from a backup of a different database. In this case, the existing database is first destroyed, and the differences regarding the names of the files in the database and the database name are ignored. (If you do not use the REPLACE option, the database system performs a safety check that guarantees an existing database is not replaced if the names of files in the database, or the database name itself, differ from the corresponding names in the backup set.)
The PARTIAL option specifies a partial restore operation. With this option you can restore a portion of a database, consisting of its primary filegroup and one or more secondary filegroups, which are specified in an additional option called FILEGROUP. (The PARTIAL option is not allowed with the RESTORE LOG statement.)
The STOPAT option allows you to restore a database to the state it was in at the exact moment before a failure occurred by specifying a point in time. The Database Engine restores all committed transactions that were recorded in the transaction log before the specified point in time. If you want to restore a database by specifying a point in time, execute the RESTORE DATABASE statement using the NORECOVERY clause. After that, execute the RESTORE LOG statement to apply each transaction log backup, specifying the name of the database, the backup device from which the transaction log backup will be restored, and the STOPAT clause. (If the backup of a log does not contain the requested time, the database will not be recovered.)
The STOPATMARK and STOPBEFOREMARK options specify to recover to a mark. This topic is described a bit later, in the section “Recovering to a Mark.”
The RESTORE DATABASE statement is also used to restore a database from a differential backup. The syntax and the options for restoring a differential backup are the same as for restoring from a full database backup. During a restoration from a differential backup, the Database Engine restores only that part of the database that has changed since the last full database backup. Therefore, restore the full database backup before you restore a differential backup!
The RESTORE LOG statement is used to perform a restore process for a transaction log. This statement has the same syntax form and the same options as the RESTORE DATABASE statement.