RESTORE (Transact-SQL)

Restores backups taken using the BACKUP command. This command enables you to:

• Restore an entire database from a full database backup (a complete restore).
• Restore part of a database (a partial restore).
• Restore specific files, filegroups, or pages to a database (a file or page restore).
• Restore a transaction log on to a database (a transaction log restore).
• Revert a database to the point in time captured by a database snapshot.

For more information about database back up and restore, see Backing Up and Restoring Databases in SQL Server.

Note

For the descriptions of the arguments, see RESTORE Arguments (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

--To restore a complete database from a full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var } 
[ FROM <backup_device> [ ,...n ] ]
[ WITH 
   [ { CHECKSUM | NO_CHECKSUM } ]
   [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
   [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ] 
   [ [ , ] KEEP_REPLICATION ] 
   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
   [ [ , ] MEDIAPASSWORD = { mediapassword |
                    @mediapassword_variable } ] 
   [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ] 
                [ ,...n ] 
   [ [ , ] PASSWORD = { password | @password_variable } ] 
    [ [ , ] BLOCKSIZE = { blocksize | @blocksize_variable } ] 
    [ [ , ] BUFFERCOUNT = { buffercount | @buffercount_variable } ] 
   [ [ , ]    MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } ] 
   [ [ , ] ENABLE_BROKER ] 
   [ [ , ] ERROR_BROKER_CONVERSATIONS ] 
   [ [ , ] NEW_BROKER ] 
   [ [ , ] { RECOVERY | NORECOVERY | STANDBY = 
          {standby_file_name | @standby_file_name_var } 
   } ] 
   [ [ , ] REPLACE ] 
   [ [ , ] RESTART ] 
   [ [ , ] RESTRICTED_USER ] 
   [ [ , ] { REWIND | NOREWIND } ] 
   [ [ , ] { UNLOAD | NOUNLOAD } ] 
   [ [ , ] STATS [ = percentage ] ] 
    [ [ , ] { STOPAT = { 'date_time' | @date_time_var } 
    |  STOPATMARK = { 'lsn:lsn_number' }
              [ AFTER 'datetime' ] 
    |  STOPBEFOREMARK = { 'lsn:lsn_number' }
             [ AFTER 'datetime' ]
   } ] 
]
[;]

<backup_device> ::=
{ 
   { logical_backup_device_name |
            @logical_backup_device_name_var }
   | { DISK | TAPE } = { 'physical_backup_device_name' |
              @physical_backup_device_name_var } 
} 

--Restore part of a database (a partial restore):
RESTORE DATABASE { database_name | @database_name_var } 
  <files_or_filegroups> [ ,...n ] 
 [ FROM <backup_device> [ ,...n ] ] 
 [ WITH 
     PARTIAL 
   [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
   [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
   [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ] 
   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
   [ [ , ] MEDIAPASSWORD = { mediapassword |
                      @mediapassword_variable } ] 
   [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ] 
                [ ,...n ] 
   [ [ , ] PASSWORD = { password | @password_variable } ] 
   [ [ , ] NORECOVERY ] 
   [ [ , ] REPLACE ] 
   [ [ , ] RESTART ] 
   [ [ , ] RESTRICTED_USER ]
   [ [ , ] { REWIND | NOREWIND } ] 
   [ [ , ] { UNLOAD | NOUNLOAD } ] 
   [ [ , ] STATS [=percentage ] ] 
   [ [ , ] { STOPAT = { 'date_time' | @date_time_var } 
    |  STOPATMARK = { 'lsn:lsn_number' }
              [ AFTER 'datetime' ] 
    |  STOPBEFOREMARK = { 'lsn:lsn_number' }
             [ AFTER 'datetime' ] 
   } ] 
]
[;]

<backup_device> ::=
{ 
   { logical_backup_device_name |
            @logical_backup_device_name_var }
   | { DISK | TAPE } = { 'physical_backup_device_name' |
              @physical_backup_device_name_var } 
} 

<files_or_filegroups> ::= 
{ 
   FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var } 
   |
   FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } 
   | 
   READ_WRITE_FILEGROUPS
} 

--To Restore Specific Files, Filegroups, or Pages: 
RESTORE DATABASE { database_name | @database_name_var } 
     <file_or_filegroup_or_pages> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ] 
[ WITH 
   [ { CHECKSUM | NO_CHECKSUM } ]
   [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
   [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ] 
   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 

   [ [ , ] MEDIAPASSWORD = { mediapassword |
                      @mediapassword_variable } ]
   [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ] 
                [ ,...n ] 
   [ [ , ] PASSWORD = { password | @password_variable } ] 
   [ [ , ] NORECOVERY ] 
   [ [ , ] REPLACE ] 
   [ [ , ] RESTART ] 
   [ [ , ] RESTRICTED_USER ]
   [ [ , ] { REWIND | NOREWIND } ] 
   [ [ , ] { UNLOAD | NOUNLOAD } ] 
   [ [ , ] STATS [ =percentage ] ] 
]
[;]

<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_or_pages> ::=
{ 
   FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
   | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } }
      | PAGE = 'file:page [ ,...n ]'  
} 

--To Restore a Transaction Log:
RESTORE LOG { database_name | @database_name_var } 
     [ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ] 
[ WITH 
   [ { CHECKSUM | NO_CHECKSUM } ]
   [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
   [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ] 
   [ [ , ] KEEP_REPLICATION ] 
   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
   [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable }      ]
   [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ] 
                [ ,...n ] 
   [ [ , ] PASSWORD = { password | @password_variable } ] 
   [ [ , ] { RECOVERY | NORECOVERY | STANDBY = 
          {standby_file_name | @standby_file_name_var } }
   ] 
   [ [ , ] REPLACE ] 
   [ [ , ] RESTART ] 
   [ [ , ] RESTRICTED_USER ]
   [ [ , ] { REWIND | NOREWIND } ] 
   [ [ , ] { UNLOAD | NOUNLOAD } ] 
   [ [ , ] STATS [=percentage ] ] 
   [ [ , ] { STOPAT = { 'date_time' | @date_time_var } 
    |  STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
              [ AFTER 'datetime' ] 
    |  STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
             [ 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_or_pages> ::=
{ 
   FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
   | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } }
      | PAGE = 'file:page [ ,...n ]'  
} 

--To Revert a Database to a Database Snapshot:   
RESTORE DATABASE { database_name | @database_name_var } 
FROM DATABASE_SNAPSHOT = database_snapshot_name 

Arguments

For descriptions of the arguments, see RESTORE Arguments (Transact-SQL).

Remarks

During an offline restore, if the specified database is in use, RESTORE forces the users off after a short delay. For online restore of a non-primary filegroup, the database can stay in use except when the filegroup being restored is being taken offline. Any data in the specified database is replaced by the restored data.

For more information about database recovery, see Understanding How Restore and Recovery of Backups Work in SQL Server and Implementing Restore Scenarios for SQL Server 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.

RESTORE can be restarted after an error. In addition, you can instruct RESTORE to continue despite errors, and it restores as much data as possible (see the CONTINUE_AFTER_ERROR option). For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups.

RESTORE is not allowed in an explicit or implicit transaction.

Restoring a damaged master database is performed using a special procedure. For more information, see Considerations for Restoring the master Database.

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server.

Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.

Restore Scenarios

SQL Server 2005 supports a variety of restore scenarios:

• Complete database restore
  Restores the entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups). For more information, see Performing a Complete Database Restore (Simple Recovery Model) or Performing a Complete Database Restore (Full Recovery Model).
• File restore
  Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored. For more information, see Performing File Restores (Full Recovery Model) and Performing File Restores (Simple Recovery Model).
• Page restore
  Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. For more information, see Performing Page Restores.
• Piecemeal restore
  Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary filegroups to be restored. For more information, see Performing Piecemeal Restores.
• Recovery only
  Recovers data that is already consistent with the database and needs only to be made available. For more information, see Recovering a Database Without Restoring Data.
• Transaction log restore.
  Under the full or bulk-logged recovery model, restoring log backups is required to reach the desired recovery point. For more information about restoring log backups, see Applying Transaction Log Backups.
• Create a mirror database.
  For more information, see How to: Prepare a Mirror Database for Mirroring (Transact-SQL).
• Create and maintain a standby server. For more information about standby servers, see Using Warm Standby Servers.

Backward Compatibility

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.

Databases Enabled for vardecimal Storage Format

Backup and restore work correctly with the vardecimal storage format, but the Database Engine must be upgraded to at least SQL Server 2005 Service Pack 2. You cannot restore the backup of a compressed database to an uncompressed database. You also cannot restore a backup of a compressed service pack 2 database to an earlier version of SQL Server. For more information about vardecimal storage format, see Storing Decimal Data As Variable Length.

Comparison of RECOVERY and NORECOVERY

Roll back is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:

• NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
  In this case, the restore sequence can restore other backups and roll them forward.
• RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.
  Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

Redoing a Restore

Undoing the effects of a restore is not possible, however, you can negate the effects of the data copy and roll forward by starting over on a per-file basis. To start over, restore the desired file and perform the roll forward again. For example, if you accidentally restored too many log backups and overshot your intended stopping point, you would have to restart the sequence.

A restore sequence can be aborted and restarted by restoring the entire contents of the affected files.

Restore Full-Text Data

In SQL Server 2005, full-text data is restored together with other database data during a complete restore. The restore operation treats full-text catalogs as files. Using the regular RESTORE DATABASE database_name FROM backup_device syntax, the full-text files are restored as part of the database file restore.

The RESTORE statement also can be used to perform restores to alternate locations, differential restores, file and filegroup restores, and differential file and filegroup restores of full-text data. In addition, RESTORE can restore full-text files only as well as with database data.

Note

You can not restore a full-text catalog to the root directory.

For more information, see Backing Up and Restoring Full-Text Catalogs.

Database Settings and Restoring

During a restore, most of the database options that are settable using ALTER DATABASE are reset to the values in force at the time of the end of backup.

Note

This behavior differs from versions of SQL Server before SQL Server 2000.

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.

Backup and Restore History Tables

SQL Server includes backup and restore history tables that track the backup and restore activity for each server instance. When a restore is performed, the backup history tables are also modified. For information on these tables, see Viewing Information About Backups.

RESTORE LOG

Beginning with SQL Server 2005, RESTORE LOG can include a file list to allow for creation of files during roll forward. This is used when the log backup contains log records written when a file was added to the database.

Note

For a database using the full or bulk-logged recovery model, SQL Server 2005 requires in most cases that you back up the tail of the log before restoring the database. Restoring a database without first backing up the tail of the log results in an error, unless the RESTORE statement contains either the WITH REPLACE or WITH STOPAT clause. For more information about tail-log backups, see Tail-Log Backups.

Online Restore

Note

Online restore is allowed only in SQL Server 2005 Enterprise Edition.

Where online restore is supported, if the database is online, file restores and page restores are automatically online restores and, also, restores of secondary filegroup after the initial stage of a piecemeal restore.

Note

Online restores can involve deferred transactions.

For more information, see Performing Online Restores.

Piecemeal Restore

Piecemeal restore, which is new in SQL Server 2005, enhances Microsoft SQL Server 2000 partial restore. Piecemeal restore allows filegroups to be restored after an initial, partial restore of the primary and some of the secondary filegroups. Filegroups that are not restored are marked as offline and are not accessible. The offline filegroups, however, can be restored later by a file restore. To allow the entire database to be restored in stages at different times, piecemeal restore maintain checks to ensure that the database will be consistent in the end.

Note

In SQL Server 2000, a partial restore can only be done from a full database backup. This restriction is removed in SQL Server 2005.

For more information, see Performing Piecemeal Restores.

Reverting a Database to a Database Snapshot

A revert database operation (specified using the DATABASE_SNAPSHOT option) takes a full source database back in time by reverting it to the time of a database snapshot, that is, overwriting the source database with data from the point in time maintained in the specified database snapshot. Only the snapshot to which you are reverting can currently exist. The revert operation then rebuilds the log (therefore, you cannot later roll forward a reverted database to the point of user error).

Data loss is confined to updates to the database since the snapshot's creation. The metadata of a reverted database is the same as the metadata at the time of snapshot creation. However, reverting to a snapshot drops all the full-text catalogs.

Reverting from a database snapshot is not intended for media recovery. Unlike a regular backup set, the database snapshot is an incomplete copy of the database files. If either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. Furthermore, even when possible, reverting in the event of corruption is unlikely to correct the problem.

Restrictions on Reverting

Reverting is unsupported under the following conditions:

• The source database contains any read-only or compressed filegroups.
• Any files are offline that were online when the snapshot was created.
• More than one snapshot of the database currently exists.

For more information, see Reverting to a Database Snapshot.

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 (for the FROM DATABASE_SNAPSHOT option, the database always exists).

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.

A backup operation may optionally specify passwords for a media set, a backup set, or both. When a password has been defined on a media set or backup set, you must specify the correct password or passwords in the RESTORE statement. These passwords prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server 2005 tools. However, password-protected media can be overwritten by the BACKUP statement's FORMAT option.

Security Note:

The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server 2005 tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password. The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created.

Examples

Note

The AdventureWorks database is shown for illustration. AdventureWorks is one of the sample databases in SQL Server 2005. Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. For more information about this database, see Samples and Sample Databases.

All the examples assume that a full database backup has been performed.

The RESTORE examples include the following:

• A. Restoring a full database
• B. Restoring full and differential database backups
• C. Restoring a database using RESTART syntax
• D. Restoring a database and move files
• E. Copying a database using BACKUP and RESTORE
• F. Restoring to a point-in-time using STOPAT
• G. Restoring the transaction log to a mark
• H. Restoring using TAPE syntax
• I. Restoring using FILE and FILEGROUP syntax
• J. Reverting from a database snapshot

Note

For additional examples, see Examples of Restore Sequences for Several Restore Scenarios and also the restore how-to topics that are listed in Backing Up and Restoring How-to Topics (Transact-SQL).

A. Restoring a full database

The following example restores a full database backup from the AdventureWorksBackups logical backup device. For an example of creating this device, see Backup Devices.

RESTORE DATABASE AdventureWorks 
   FROM AdventureWorksBackups

Note

For a database using the full or bulk-logged recovery model, SQL Server 2005 requires in most cases that you back up the tail of the log before restoring the database. For more information, see Tail-Log Backups.

[Top of examples]

B. Restoring full and differential database backups

The following example restores a full database backup followed by a differential backup from the Z:\SQLServerBackups\AdventureWorks.bak backup device, which contains both backups. The full database backup to be restored is the sixth backup set on the device (FILE = 6), and the differential database backup is the ninth backup set on the device (FILE = 9). As soon as the differential backup is recovered, the database is recovered.

RESTORE DATABASE AdventureWorks
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
   WITH FILE = 6
      NORECOVERY;
RESTORE DATABASE AdventureWorks
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
   WITH FILE = 9
      RECOVERY;

[Top of examples]

C. Restoring a database using RESTART syntax

The following 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 AdventureWorks
   FROM AdventureWorksBackups
-- Here is the RESTORE RESTART operation.
RESTORE DATABASE AdventureWorks 
   FROM AdventureWorksBackups WITH RESTART

[Top of examples]

D. Restoring a database and move files

The following example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.

RESTORE DATABASE AdventureWorks
   FROM AdventureWorksBackups
   WITH NORECOVERY, 
      MOVE 'AdventureWorks_Data' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewAdvWorks.mdf', 
      MOVE 'AdventureWorks_Log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewAdvWorks.ldf'
RESTORE LOG AdventureWorks
   FROM AdventureWorksBackups
   WITH RECOVERY

[Top of examples]

E. Copying a database using BACKUP and RESTORE

The following example uses both the BACKUP and RESTORE statements to make a copy of the AdventureWorks 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 (Transact-SQL).

BACKUP DATABASE AdventureWorks 
   TO AdventureWorksBackups ;

RESTORE FILELISTONLY 
   FROM AdventureWorksBackups ;

RESTORE DATABASE TestDB 
   FROM AdventureWorksBackups 
   WITH MOVE 'AdventureWorks_Data' TO 'C:\MySQLServer\testdb.mdf',
   MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\testdb.ldf';
GO

[Top of examples]

F. Restoring to a point-in-time using STOPAT

The following example restores a database to its state as of 12:00 AM on April 15, 2020 and shows a restore operation that involves multiple logs and multiple backup devices.

RESTORE DATABASE AdventureWorks
   FROM AdventureWorksBackups
   WITH NORECOVERY;

RESTORE LOG AdventureWorks
   FROM AdventureWorksBackups
   WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

RESTORE LOG AdventureWorks
   FROM AdventureWorksBackups
   WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

[Top of examples]

G. Restoring the transaction log to a mark

The following example restores the transaction log to the mark in the marked transaction named ListPriceUpdate.

USE AdventureWorks
GO
BEGIN TRANSACTION ListPriceUpdate
   WITH MARK 'UPDATE Product list prices';
GO

UPDATE Production.Product
   SET ListPrice = ListPrice * 1.10
   WHERE ProductNumber LIKE 'BK-%';
GO

COMMIT TRANSACTION ListPriceUpdate;
GO

-- Time passes. Regular database 
-- and log backups are taken.
-- An error occurs in the database.
USE master
GO

RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE = 3, NORECOVERY;
GO

RESTORE LOG AdventureWorks
   FROM AdventureWorksBackups 
   WITH FILE = 4,
   RECOVERY, 
   STOPATMARK = 'ListPriceUpdate';

[Top of examples]

H. Restoring using TAPE syntax

The following example restores a full database backup from a TAPE backup device.

RESTORE DATABASE AdventureWorks 
   FROM TAPE = '\\.\tape0'

[Top of examples]

I. Restoring using FILE and FILEGROUP syntax

The following example restores a database named MyDatabase that has two files, one secondary filegroup, and one transaction log. The database uses the full recovery model.

The database backup is the ninth backup set in the media set on a logical backup device named MyDatabaseBackups. Next, three log backups, which are in the next three backup sets (10, 11, and 12) on the MyDatabaseBackups device, are restored by using WITH NORECOVERY. After restoring the last log backup, the database is recovered.

Note

Recovery is performed as a separate step to reduce the possibility of you recovering too early, before all of the log backups have been restored.

In the RESTORE DATABASE, notice that there are two types of FILE options. The FILE options preceding the backup device name specify the logical file names of the database files that are to be restored from the backup set; for example, FILE = 'MyDatabase_data_1'. This backup set is not the first database backup in the media set; therefore, its position the media set is indicated by using the FILE option in the WITH clause, FILE=9.

RESTORE DATABASE MyDatabase
   FILE = 'MyDatabase_data_1',
   FILE = 'MyDatabase_data_2',
   FILEGROUP = 'new_customers'
   FROM MyDatabaseBackups
   WITH 
      FILE = 9,
      NORECOVERY;
GO
-- Restore the log backups.
RESTORE LOG MyDatabase
   FROM MyDatabaseBackups
   WITH FILE = 10, 
      NORECOVERY;
GO
RESTORE LOG MyDatabase
   FROM MyDatabaseBackups
   WITH FILE = 11, 
      NORECOVERY;
GO
RESTORE LOG MyDatabase
   FROM MyDatabaseBackups
   WITH FILE = 12, 
      NORECOVERY;
GO
--Recover the database:
RESTORE DATABASE MyDatabase WITH RECOVERY;
GO

[Top of examples]

J. Reverting from a database snapshot

The following example reverts a database to a database snapshot. The example assumes that only one snapshot currently exists on the database. For an example of how to create this database snapshot, see How to: Create a Database Snapshot (Transact-SQL).

Note

Reverting to a snapshot drops all the full-text catalogs.

USE master  
RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';
GO

For more information, see Reverting to a Database Snapshot.

[Top of examples]

See Also

Reference

BACKUP (Transact-SQL)
RESTORE REWINDONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL)

Other Resources

Backing Up and Restoring Full-Text Catalogs
Backing Up and Restoring Replicated Databases
Implementing Restore Scenarios for SQL Server Databases
Media Sets, Media Families, and Backup Sets
Understanding How Restore and Recovery of Backups Work in SQL Server
Viewing Information About Backups

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added the BLOCKSIZE, BUFFERCOUNT options MAXTRANSFERSIZE options to the Syntax section. Corrected STOPATMARK and STOPBEFOREMARK syntax for RESTORE DATABASE. Added a section to the Remarks about how to restore SQL Server 2005, service pack 2 databases enabled for vardecimal storage format. Added a section to the Remarks about clearing the plan cache. Changed content: Corrected STOPAT, STOPATMARK, and STOPBEFOREMARK syntax.
14 April 2006	Changed content: Clarified the database snapshot restriction for offline files. Changed the syntax for the logical name of a backed up file from logical_file_name variable name to logical_file_name_in_backup.