RESTORE
Restores backups taken using the BACKUP command. For more information about database back up and restore operations, see Backing Up and Restoring Databases.
Syntax
Restore an entire database:
RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ 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 ] ]
]
Restore part of a database:
RESTORE DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
{ PARTIAL }
[ [ , ] 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 ]
[ [ , ] NORECOVERY ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] RESTART ]
[ [ , ] STATS [= percentage ] ]
]
Restore specific files or filegroups:
RESTORE DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ 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 ]
[ [ , ] NORECOVERY ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
Restore a transaction log:
RESTORE LOG { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [= percentage ] ]
[ [ , ] STOPAT = { date_time | @date_time_var }
| [ , ] STOPATMARK = 'mark_name' [ AFTER datetime ]
| [ , ] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ]
]
]
< backup_device > ::=
{
{ 'logical_backup_device_name' | @logical_backup_device_name_var }
| { DISK | TAPE } =
{ 'physical_backup_device_name' | @physical_backup_device_name_var }
}
< file_or_filegroup > ::=
{
FILE = { logical_file_name | @logical_file_name_var }
|
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
}
Arguments
DATABASE
Specifies the complete restore of the database from a backup. If a list of files and filegroups is specified, only those files and filegroups are restored.
{database_name | @database_name_var}
Is the database that the log or complete database is restored into. If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var = database name) or as a variable of character string data type, except for the ntext or text data types.
FROM
Specifies the backup devices from which to restore the backup. If the FROM clause is not specified, the restore of a backup does not take place. Instead, the database is recovered. Omitting the FROM clause can be used to attempt recovery of a database that has been restored with the NORECOVERY option, or to switch over to a standby server. If the FROM clause is omitted, NORECOVERY, RECOVERY, or STANDBY must be specified.
<backup_device>
Specifies the logical or physical backup devices to use for the restore operation. Can be one or more of the following:
- {'logical_backup_device_name' | @logical_backup_device_name_var}
- Is the logical name, which must follow the rules for identifiers, of the backup device(s) created by sp_addumpdevice from which the database is restored. If supplied as a variable (@logical_backup_device_name_var), the backup device name can be specified either as a string constant (@logical_backup_device_name_var = logical_backup_device_name) or as a variable of character string data type, except for the ntext or text data types.
- {DISK | TAPE } =
'physical_backup_device_name' | @physical_backup_device_name_var - Allows backups to be restored from the named disk or tape device. The device types of disk and tape should be specified with the actual name (for example, complete path and file name) of the device: DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'. If specified as a variable (@physical_backup_device_name_var), the device name can be specified either as a string constant (@physical_backup_device_name_var = 'physcial_backup_device_name') or as a variable of character string data type, except for the ntext or text data types.
If using either a network server with a UNC name or a redirected drive letter, specify a device type of disk. The account under which you are running SQL Server must have READ access to the remote computer or network server in order to perform a RESTORE operation.
n
Is a placeholder that indicates multiple backup devices and logical backup devices can be specified. The maximum number of backup devices or logical backup devices is 64.
RESTRICTED_USER
Restricts access for the newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In SQL Server 2000, RESTRICTED_USER replaces the DBO_ONLY option. DBO_ONLY is available only for backward compatibility.
Use with the RECOVERY option.
For more information, see Setting Database Options.
FILE = { file_number | @file_number }
Identifies the backup set to be restored. For example, a file_number of 1 indicates the first backup set on the backup medium and a file_number of 2 indicates the second backup set.
PASSWORD = { password | @password_variable }
Provides the password for the backup set. PASSWORD is a character string. If a password was provided when the backup set was created, the password must be supplied to perform any restore operation from the backup set.
For more information about using passwords, see Permissions.
MEDIANAME = {media_name | @media_name_variable}
Specifies the name for the media. If provided, the media name must match the media name on the backup volume(s); otherwise, the restore operation terminates. If no media name is given in the RESTORE statement, the check for a matching media name on the backup volume(s) is not performed.
Important Consistently using media names in backup and restore operations provides an extra safety check for the media selected for the restore operation.
MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
Supplies the password for the media set. MEDIAPASSWORD is a character string.
If a password was provided when the media set was formatted, that password must be supplied to access any backup set on that media set.
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to operating_system_file_name. By default, the logical_file_name is restored to its original location. If the RESTORE statement is used to copy a database to the same or different server, the MOVE option may be needed to relocate the database files and to avoid collisions with existing files. Each logical file in the database can be specified in different MOVE statements.
Note Use RESTORE FILELISTONLY to obtain a list of the logical files from the backup set.
For more information, see Copying Databases.
n
Is a placeholder that indicates more than one logical file can be moved by specifying multiple MOVE statements.
NORECOVERY
Instructs the restore operation to not roll back any uncommitted transactions. Either the NORECOVERY or STANDBY option must be specified if another transaction log has to be applied. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).
Note When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
When used with a file or filegroup restore operation, NORECOVERY forces the database to remain in the restoring state after the restore operation. This is useful in either of these situations:
- A restore script is being run and the log is always being applied.
- A sequence of file restores is used and the database is not intended to be usable between two of the restore operations.
RECOVERY
Instructs the restore operation to roll back any uncommitted transactions. After the recovery process, the database is ready for use.
If subsequent RESTORE operations (RESTORE LOG, or RESTORE DATABASE from differential) are planned, NORECOVERY or STANDBY should be specified instead.
If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default. When restoring backup sets from an earlier version of SQL Server, a database upgrade may be required. This upgrade is performed automatically when WITH RECOVERY is specified. For more information, see Transaction Log Backups .
STANDBY = undo_file_name
Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.
If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it.
The same undo file can be used for consecutive restores of the same database. For more information, see Using Standby Servers.
Important If free disk space is exhausted on the drive containing the specified undo file name, the restore operation stops.
STANDBY is not allowed when a database upgrade is necessary.
KEEP_REPLICATION
Instructs the restore operation to preserve replication settings when restoring a published database to a server other than that on which it was created. KEEP_REPLICATION is to be used when setting up replication to work with log shipping. It prevents replication settings from being removed when a database or log backup is restored on a warm standby server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.
NOUNLOAD
Specifies that the tape is not unloaded automatically from the tape drive after a RESTORE. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices. If a non-tape device is being used for RESTORE, this option is ignored.
NOREWIND
Specifies that SQL Server will keep the tape open after the backup operation. Keeping the tape open prevents other processes from accessing the tape. The tape will not be released until a REWIND or UNLOAD statement is issued, or the server is shut down. A list of currently open tapes can be found by querying the sysopentapes table in the master database.
NOREWIND implies NOUNLOAD. This option is used only for tape devices. If a non-tape device is being used for RESTORE, this option is ignored.
REWIND
Specifies that SQL Server will release and rewind the tape. If neither NOREWIND nor REWIND is specified, REWIND is the default. This option is used only for tape devices. If a non-tape device is being used for RESTORE, this option is ignored.
UNLOAD
Specifies that the tape is automatically rewound and unloaded when the RESTORE is finished. UNLOAD is set by default when a new user session is started. It remains set until NOUNLOAD is specified. This option is used only for tape devices. If a non-tape device is being used for RESTORE, this option is ignored.
REPLACE
Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs (which prevents overwriting a different database by accident). The safety check ensures that the RESTORE DATABASE statement will not restore the database to the current server if:
- The database named in the RESTORE statement already exists on the current server, and
- The database name is different from the database name recorded in the backup set.
REPLACE also allows RESTORE to overwrite an existing file which cannot be verified as belonging to the database being restored. Normally, RESTORE will refuse to overwrite pre-existing files.
RESTART
Specifies that SQL Server should restart a restore operation that has been interrupted. RESTART restarts the restore operation at the point it was interrupted.
Important This option can only be used for restores directed from tape media and for restores that span multiple tape volumes.
STATS [= percentage]
Displays a message each time another percentage completes and is used to gauge progress. If percentage is omitted, SQL Server displays a message after every 10 percent completed.
PARTIAL
Specifies a partial restore operation. Application or user errors often affect an isolated portion of the database, such as a table. Examples of this type of error include an invalid update or a table dropped by mistake. To support recovery from these events, SQL Server provides a mechanism to restore part of the database to another location so that the damaged or missing data can be copied back to the original database.
The granularity of the partial restore operation is the database filegroup. The primary file and filegroup are always restored, along with the files that you specify and their corresponding filegroups. The result is a subset of the database. Filegroups that are not restored are marked as offline and are not accessible.
For more information, see Partial Database Restore Operations.
<file_or_filegroup>
Specifies the names of the logical files or filegroups to include in the database restore. Multiple files or filegroups can be specified.
- FILE = {logical_file_name | @logical_file_name_var}
- Names one or more files to include in the database restore.
- FILEGROUP = {logical_filegroup_name | @logical_filegroup_name_var}
- Names one or more filegroups to include in the database restore.
When this option is used, the transaction log must be applied to the database files after the last file or filegroup restore operation to roll the files forward to be consistent with the rest of the database. If none of the files being restored have been modified since they were last backed up, a transaction log does not have to be applied. The RESTORE statement informs the user of this situation.
n
Is a placeholder indicating that multiple files and filegroups may be specified. There is no maximum number of files or filegroups.
LOG
Specifies that a transaction log backup is to be applied to this database. Transaction logs must be applied in sequential order. SQL Server checks the backed up transaction log to ensure that the transactions are being loaded into the correct database and in the correct sequence. To apply multiple transaction logs, use the NORECOVERY option on all restore operations except the last. For more information, see Transaction Log Backups.
STOPAT = date_time | @date_time_var
Specifies that the database be restored to the state it was in as of the specified date and time. If a variable is used for STOPAT, the variable must be varchar, char, smalldatetime, or datetime data type. Only transaction log records written before the specified date and time are applied to the database.
Note If you specify a STOPAT time that is beyond the end of the RESTORE LOG operation, the database is left in an unrecovered state, just as if RESTORE LOG had been run with NORECOVERY.
STOPATMARK = 'mark_name' [ AFTER datetime ]
Specifies recovery to the specified mark, including the transaction that contains the mark. If AFTER datetime is omitted, recovery stops at the first mark with the specified name. If AFTER datetime is specified, recovery stops at the first mark having the specified name exactly at or after datetime.
STOPBEFOREMARK = 'mark_name' [ AFTER datetime ]
Specifies recovery to the specified mark but does not include the transaction that contains the mark. If AFTER datetime is omitted, recovery stops at the first mark with the specified name. If AFTER datetime is specified, recovery stops at the first mark having the specified name exactly at or after datetime.
Remarks
During the restore, the specified database must not be in use. Any data in the specified database is replaced by the restored data.
For more information about database recovery, see Backing Up and Restoring Databases.
Cross-platform restore operations, even between different processor types, can be performed as long as the collation of the database is supported by the operating system. For more information, see SQL Server Collation Fundamentals.
Restore Types
Here are the types of restores that SQL Server supports:
- Full database restore which restores the entire database.
- Full database restore and a differential database restore. Restore a differential backup by using the RESTORE DATABASE statement.
- Transaction log restore.
- Individual file and filegroup restores. Files and filegroups can be restored either from a file or filegroup backup operation, or from a full database backup operation. When restoring files or filegroups, you must apply a transaction log. In addition, file differential backups can be restored after a full file restore.
For more information, see Transaction Log Backups.
- Create and maintain a warm standby server or standby server. For more information about standby servers, see Using Standby Servers.
To maintain backward compatibility, the following keywords can be used in the RESTORE statement syntax:
- LOAD keyword can be used in place of the RESTORE keyword.
- TRANSACTION keyword can be used in place of the LOG keyword.
- DBO_ONLY keyword can be used in place of the RESTRICTED_USER keyword.
Database Settings and Restoring
When using the RESTORE DATABASE statement, the restorable database options (which are all the settable options of ALTER DATABASE except offline and the merge publish, published, and subscribed replication options) are reset to the settings in force at the time the BACKUP operation ended.
Note This behavior differs from earlier versions of Microsoft SQL Server.
Using the WITH RESTRICTED_USER option, however, overrides this behavior for the user access option setting. This setting is always set following a RESTORE statement, which includes the WITH RESTRICTED_USER option.
For more information, see Backing Up and Restoring Replication Databases.
Restore History Tables
SQL Server includes the following restore history tables, which track the RESTORE activity for each computer system:
Note When a RESTORE is performed, the backup history tables are modified.
Restoring a damaged master database is performed using a special procedure. For more information, see Restoring the master Database.
Backups created with Microsoft® SQL Server™ 2000 cannot be restored to an earlier version of SQL Server.
Permissions
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.
In addition, the user may specify passwords for a media set, a backup set, or both. When a password is defined on a media set, it is not enough that a user is a member of appropriate fixed server and database roles to perform a backup. The user also must supply the media password to perform these operations. Similarly, RESTORE is not allowed unless the correct media password and backup set password are specified in the restore command.
Defining passwords for backup sets and media sets is an optional feature in the BACKUP statement. The passwords will prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server 2000 tools, but passwords do not prevent overwrite of media with the FORMAT option.
Thus, although the use of passwords can help protect the contents of media from unauthorized access using SQL Server tools, passwords do not protect contents from being destroyed. Passwords do not fully prevent unauthorized access to the contents of the media because the data in the backup sets is not encrypted and could theoretically be examined by programs specifically created for this purpose. For situations where security is crucial, it is important to prevent access to the media by unauthorized individuals.
It is an error to specify a password if none is defined.
Examples
Note All examples assume that a full database backup has been performed.
A. Restore a full database
Note The MyNwind database is shown for illustration.
This example restores a full database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1
B. Restore a full database and a differential backup
This example restores a full database backup followed by a differential backup. In addition, this example shows restoring the second backup set on the media. The differential backup was appended to the backup device that contains the full database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH FILE = 2
C. Restore a database using RESTART syntax
This example uses the RESTART option to restart a RESTORE operation interrupted by a server power failure.
-- This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE MyNwind
FROM MyNwind_1
-- Here is the RESTORE RESTART operation.
RESTORE DATABASE MyNwind
FROM MyNwind_1 WITH RESTART
D. Restore a database and move files
This example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL\Data directory.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH RECOVERY
E. Make a copy of a database using BACKUP and RESTORE
This example uses both the BACKUP and RESTORE statements to make a copy of the Northwind database. The MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The new copy of the database is named TestDB. For more information, see RESTORE FILELISTONLY.
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO
F. Restore to a point-in-time using STOPAT syntax and restore with more than one device
This example restores a database to its state as of 12:00 A.M. on April 15, 1998, and shows a restore operation that involves multiple logs and multiple backup devices.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
G. Restore using TAPE syntax
This example restores a full database backup from a TAPE backup device.
RESTORE DATABASE MyNwind
FROM TAPE = '\\.\tape0'
H. Restore using FILE and FILEGROUP syntax
This example restores a database with two files, one filegroup, and one transaction log.
RESTORE DATABASE MyNwind
FILE = 'MyNwind_data_1',
FILE = 'MyNwind_data_2',
FILEGROUP = 'new_customers'
FROM MyNwind_1
WITH NORECOVERY
-- Restore the log backup.
RESTORE LOG MyNwind
FROM MyNwindLog1
I. Restore the Transaction Log to the Mark
This example restores the transaction log to the mark named "RoyaltyUpdate."
BEGIN TRANSACTION RoyaltyUpdate
WITH MARK 'Update royalty values'
GO
USE pubs
GO
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'PC%'
GO
COMMIT TRANSACTION RoyaltyUpdate
GO
--Time passes. Regular database
--and log backups are taken.
--An error occurs.
USE master
GO
RESTORE DATABASE pubs
FROM Pubs1
WITH FILE = 3, NORECOVERY
GO
RESTORE LOG pubs
FROM Pubs1
WITH FILE = 4,
STOPATMARK = 'RoyaltyUpdate'