The RESTORE Database examples include the following:
A. Restoring a full database
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
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.
|
B. Restoring full and differential database backups
The following example restores a full database backup followed by a differential backup from theZ:\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;
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
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
E. Copying a database using BACKUP and RESTORE
The following example uses both the BACKUP and RESTORE statements to make a copy of theAdventureWorks 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
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
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';
G. Restoring the transaction log to a mark
The following example restores the transaction log to the mark in the marked transaction namedListPriceUpdate.
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';
H. Restoring using TAPE syntax
The following example restores a full database backup from a TAPE backup device.
RESTORE DATABASE AdventureWorks
FROM TAPE = '\\.\tape0'
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 namedMyDatabaseBackups. 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
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
