Wednesday, January 21, 2015

When was your SQL Server database last restored?

Just a real quick post... I took on a new project very recently, and I was doing an assessment of their farm.  Last DBCC's, last backups... even last restores.   I had several different statements, but I returned multiple restore dates, when the database data and log file were restored in separate operations.  This statement takes that into account, and just gives you back a single entry, for the most recent restore date for your databases.  It also tells you from what file they were restored.  Check it out.  Let me know what you think.

When was your database last restored? 
From what file?                                                              */

     WITH LastRestored AS
            rh.destination_database_name [DatabaseName],
            rh.restore_date [Restored],
            bmf.physical_device_name [SourceFile],
            RowNum = ROW_NUMBER() OVER (PARTITION BY bs.database_name 
                  ORDER BY rh.restore_date DESC )
           msdb..restorehistory rh INNER JOIN msdb..backupset bs 
               ON rh.backup_set_id = bs.backup_set_id INNER JOIN msdb..backupmediafamily bmf 
   ON bs.media_set_id = bmf.media_set_id
     SELECT DatabaseName,Restored,SourceFile
     FROM LastRestored
     WHERE RowNum = 1

Your results will look a little something like this:

   DatabaseName                Restored                               SourceFile
   AdventureWorks2012      2015-01-20 21:59:41.130     C:\Backups\AdventureWorks2012_Backup.bak
   ReportServer              2015-01-20 11:39:33.853     C:\SSRS\ReportServerLog.bak
   ReportServerTempDB      2015-01-20 11:39:43.433     C:\SSRS\ReportServerTempDBLog.bak

Here are a couple of references with greater detail on the tables I am targeting:


No comments:

Post a Comment