|
Execute the following
Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to find the database restore dates and restore sample databases from database backup files:
-- RESTORE DATABASE to a new location syntax
RESTORE DATABASE [NEWAdventureWorks] FROM DISK = N'F:\temp\AW.bak'
WITH -- FILE = 6,
MOVE N'AdventureWorks_Data' TO N'F:\data\AW.mdf',
MOVE N'AdventureWorks_Log' TO N'F:\data\log\AW8.ldf',
NOUNLOAD, STATS = 10
GO
------------
-- Query msdb system database RestoreHistory and related tables
use msdb;
select
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_date
from RestoreHistory h
inner join BackupSet b
on h.backup_set_id = b.backup_set_id
inner join BackupFile f
on f.backup_set_id = b.backup_set_id
order by RestoreDate
go
/* Partial results
DBRestored RestoreDate SourceDB SourceFile
CopyOfpubs 2011-11-02 10:03:46.717 pubs F:\DATA\db\pubs.mdf
CopyOfpubs 2011-11-02 10:03:46.717 pubs F:\DATA\db\pubs_log.LDF
*/
------------
-- SAMPLE DATABASE RESTORE SCRIPT - new db is created from pubs
-- SQL restore database command
USE master;
RESTORE DATABASE CopyOfpubs
FROM DISK = N'F:\DATA\db\Backup\pubs.bak'
WITH FILE = 3,
MOVE N'pubs' TO N'F:\DATA\sql\file\CopyOfpubs.mdf',
MOVE N'pubs_log' TO N'F:\DATA\sql\file\CopyOfpubs_log.LDF',
NOUNLOAD, STATS = 10
GO
------------
-- SQL Server restoring new CopyOmegaAW2008 database from the backup of AlphaAW2008
RESTORE DATABASE [CopyOmegaAW2008]
FROM DISK = N'F:\data\AdventureWorks2008\backup\AlphaAW2008.bak'
WITH FILE = 1,
MOVE N'AdventureWorks2008_Data'
TO N'F:\DATA\AdventureWorks2008\DATA\CopyOmegaAW2008.mdf',
MOVE N'AdventureWorks2008_Log'
TO N'F:\DATA\AdventureWorks2008\DATA\CopyOmegaAW2008_1.ldf',
MOVE N'FileStreamDocuments'
TO N'F:\DATA\AdventureWorks2008\DATA\CopyOmegaAW2008_2.Documents',
NOUNLOAD, STATS = 10
GO
/* Messages
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 23024 pages for database 'CopyOmegaAW2008', file 'AdventureWorks2008_Data' on file 1.
Processed 8 pages for database 'CopyOmegaAW2008', file 'AdventureWorks2008_Log' on file 1.
Processed 36 pages for database 'CopyOmegaAW2008', file 'FileStreamDocuments' on file 1.
RESTORE DATABASE successfully processed 23067 pages in 6.289 seconds (28.654 MB/sec).
*/
------------
Related article:
RESTORE (Transact-SQL)
|
