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:

-- 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)