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 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
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 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
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
No comments:
Post a Comment