Restores an entire database and transaction log, database file(s), or a transaction log. For more information about database back up and restore operations, see Backing Up and Restoring Databases.
Caution Backups created with Microsoft® SQL Server™ version 7.0 cannot be restored to an earlier version of SQL Server.
Restore an entire database:
RESTORE DATABASE {database_name | @database_name_var}
[FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] MOVE 'logical_file_name' TO 'operating_system_file_name']
[,...n]
[[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE]
[[,] RESTART]
[[,] STATS [= percentage]]
]
Restore specific files or filegroups:
RESTORE DATABASE {database_name | @database_name_var}
<file_or_filegroup> [,...n]
[FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] NORECOVERY]
[[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE]
[[,] RESTART]
[[,] STATS [= percentage]]
]
Restore a transaction log:
RESTORE LOG {database_name | @database_name_var}
[FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] RESTART]
[[,] STATS [= percentage]]
[[,] STOPAT = {date_time | @date_time_var}]
]
<backup_device> ::=
{
{'backup_device_name' | @backup_device_name_var}
| {DISK | TAPE | PIPE} =
{'temp_backup_device' | @temp_backup_device_var}
}
<file_or_filegroup> ::=
{
FILE = {logical_file_name | @logical_file_name_var}
|
FILEGROUP = {logical_filegroup_name | @logical_filegroup_name_var}
}
Allows backups to be restored from the named disk, tape, or pipe 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:\Mssql7\Backup\Mybackup.dat’ or TAPE = \\.\TAPE0. A device type of pipe should specify the name of the named pipe that is used by the client application. If specified as a variable (@temp_backup_device_var), the device name can be specified either as a string constant (@temp_backup_device_var = ‘temp_backup_device’) or as a variable of character string data type, except for the ntext or text data types.
Pipe devices have been added to allow third-party vendors a flexible way to connect their own software. The pipe device is not used for typical Transact-SQL use.
If you are using either a network server with a UNC name or a redirected drive letter, specify a device type of disk.
Important Consistently using media names in backup and restore operations provides an extra safety check for the media selected for the restore operation.
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 load state after the restore operation. This is useful in either of these situations:
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. The database version is updated for databases and transaction logs if RECOVERY is specified. For more information, see Creating and Applying Transaction Log Backups.
STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm backup server situations or special recovery situations in which it is useful to check 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 unless the file contains current undo information for a database.
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.
Or
The set of files in the database is different from the set of database files contained in the backup set. Differences in file size are ignored.
When used with a file or filegroup restore operation, REPLACE allows overwriting of an existing file, which is useful only when attempting to restore a file on a disk that is replacing a failed disk.
Important This option can only be used for restores directed from tape media and for restores that span multiple tape volumes.
When this option is used, the transaction log must be applied to the database files immediately after the last file or filegroup restore operation to roll the files forward to be consistent with the rest of the database. If no 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.
The RESTORE statement requires that one or more filegroups be restored in a single operation if indexes were created on these filegroups since the last time these files were backed up. This requirement of restoring the entire set of filegroup(s) is necessary whether the restore is from a filegroup backup or a full database backup. The RESTORE statement detects this filegroup situation and reports the minimum filegroups that must be restored.
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.
During the restore, the specified database must not be in use. Any data in the specified database is replaced by the restored data.
Cross-platform restores, even between different processor types, can be performed if the following sort order and character set restrictions are met:
For more information about database recovery, see Backing Up and Restoring Databases.
Here are the types of restores that SQL Server supports:
Important To maintain backward compatibility, the LOAD keyword can be used in place of the RESTORE keyword in the RESTORE statement syntax. In addition, the TRANSACTION keyword can be used in place of the LOG keyword in the RESTORE statement syntax.
When you use the RESTORE DATABASE statement, the restorable database options (which are all the settable options of sp_dboption 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 DBO_ONLY option, however, overrides this behavior for the dbo use only setting. This setting is always set following a RESTORE statement which includes the WITH DBO_ONLY option.
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 the master database because it has been damaged is performed using a special procedure. For more information, see Restoring the master Database.
Caution VOLUME is not an available option in SQL Server version 7.0. If VOLUME is specified, SQL Server returns an error message and halts the operation.
If the database being restored does not exist, the user must have CREATE DATABASE permissions. If the database does exist, RESTORE permissions default to members of the sysadmin fixed server role and the db_owner fixed database role.
Note All examples assume that a full database backup has been performed earlier.
Note The MyNwind database is shown for illustration.
This example restores a full database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1
This example restores a full database backup followed by a differential backup restore. 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
This example uses the RESTART option to restart a RESTORE operation that was 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
This example restores a full database and transaction log and moves the restored database into the C:\Mssql7\Data directory.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind' TO 'c:\mssql7\data\NewNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:\mssql7\data\NewNwind.ldf'
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH RECOVERY
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
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'
This example restores a full database backup from a DISK backup device.
RESTORE DATABASE MyNwind
FROM DISK = 'c:\mssql7\backup\MyNwind.bak'
This example restores a full database backup from a TAPE backup device.
RESTORE DATABASE MyNwind
FROM TAPE = '\\.\tape0'
This example illustrates restoring 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
| BACKUP | RESTORE HEADERONLY |
| bcp Utility | RESTORE LABELONLY |
| Data Types | RESTORE VERIFYONLY |
| Using Identifiers | sp_addumpdevice |
| RESTORE FILELISTONLY | Understanding Media Sets and Families |