Determine WHEN and WHAT FILE was LAST Used to RESTORE a SQL SERVER Database

Sometimes you've restored a database from a production copy or whatever, and you move on to a different project.  Time goes by, and someone needs to run something on that database, but they need to know if the database has already been restored from a recent copy of production...  How do you find out when the last time a database was restored and what file was used to restore it?

This little script has been a HUGE time saver.  I use this script to determine if I need to have our IT group backup and download a copy of a production database.  If the database I'm looking at was recently refreshed from production already, I don't need them to waste that kind of time.  That can save us up to literally 24 hours in wasted time in some cases.

To run this, copy and paste this code into SQL Server Management Studio 2005 or greater (I think this works with SQL 2000 as well with Query Analyzer).  Don't forget to make sure you have the database selected in the dropdown list for which you want to find out this information:

DECLARE @backup_finish_date DATETIME
DECLARE @source_database_name VARCHAR(100)
DECLARE @restore_date DATETIME
DECLARE @backup_file_used_for_restore VARCHAR(500)

SELECT TOP 1 @restore_date = [rs].[restore_date],
             @backup_finish_date = [bs].[backup_finish_date],
             @source_database_name = [bs].[database_name],
             @backup_file_used_for_restore = [bmf].[physical_device_name]
FROM   msdb..restorehistory rs
       INNER JOIN msdb..backupset bs
         ON [rs].[backup_set_id] = [bs].[backup_set_id]
       INNER JOIN msdb..backupmediafamily bmf
         ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE  [rs].[destination_database_name] = Db_name()
ORDER  BY [rs].[restore_date] DESC

PRINT @@SERVERNAME + ' : ' + Db_name()

PRINT 'was restored on: ' + Isnull(CAST(@restore_date AS VARCHAR), 'No Date')

PRINT 'from a backup of database: ' + Isnull(@source_database_name, 'No Name')

PRINT 'taken on: ' + Isnull(CAST(@backup_finish_date AS VARCHAR), 'No Date')

PRINT 'in file: ' + Isnull(@backup_file_used_for_restore, 'No Name')  

The results will typically look like this:

CCGTESTDB\SQL2005 : stIowaProd
was restored on: Dec  5 2011  1:15PM
from a backup of database: db_CNPProd
taken on: Nov  8 2011  1:49PM
in file: E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Iowa\db_CNPProd_201111081349\db_CNPProd_201111081349.bak


How do you determine this information?  Any better solution, please comment.

5 All-Time Greatest Toys

The holidays come every year, and we stress over toys and gifts.  But some toys will always be the best...  follow to see the 5 all-time greatest toys ever.

FeetDog News