datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find out the last restore date for a database?

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)

 

Exam Prep 70-461
Exam 70-461