When was the last time your SQL Server database was restored


By:   |   Updated: 2009-04-07   |   Comments (12)   |   Related: More > Restore


Problem
Often times we are asked the question "when was the last time my database was restored, and where was it restored from?"  In this tip, we will look at some of the system tables that capture restore history information and how you can query these system tables to answer this question.

Solution
The restore history information is readily available inside the msdb, making the solution as easy as a few lines of T-SQL.


Returning the details

Here is some T-SQL that will return information about the last time a database has been restored. There are two variables, @dbname and @days, that you can configure. The first (@dbname) would be the name of the database you are searching for and would need to be enclosed in single quotation marks. If you leave it NULL than all databases will be returned. The second variable (@days) would be a negative integer (i.e., -7) which represents how many days previously you want to search. So, -7 would translate to returning the previous week's worth of history. If you leave it NULL then the script will default to searching for only the previous thirty days.

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type 
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From], 
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO

The script will return the following result set:

Here is the definition of each of the result set columns.

Column Name Description
Database The name of the target database.
Restored By The name of the user that performed the restore.
Restore Type The type of restore performed. The possible types include the following:
  • D - Database
  • F - File
  • G - Filegroup
  • I - Differential
  • L - Log
  • V - Verifyonly
  • R - Revert
Restore Started The time at which the restore command was started.
Restored From The file(s) that the restore used in the RESTORE command.
Restored To The database data files restored (or created) as a result of the RESTORE command.

Next Steps



Last Updated: 2009-04-07


get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips
Related Resources





Comments For This Article




Sunday, November 13, 2016 - 3:58:25 PM - will armer Back To Top (43754)

-- I found this somewhere on the internet and it works well:

Select

Destination_database_name, restore_date, database_name as Source_database, Physical_device_name as Backup_file_used_to_restore, bs.user_name, bs.machine_name from msdb.dbo.restorehistory rh inner join msdb.dbo.backupset bs on rh.backup_set_id=bs.backup_set_id inner join msdb.dbo.backupmediafamily bmf on bs.media_set_id =bmf.media_set_id ORDER BY [rh].[restore_date] DESC


Thursday, February 26, 2015 - 3:24:41 AM - David Back To Top (36354)

Thank you very much!


Monday, November 17, 2014 - 9:24:35 PM - Doug Back To Top (35317)

Great script to get the last time a non-prod database was refreshed.


Monday, November 03, 2014 - 6:08:39 PM - Aner Bautista Back To Top (35174)

You made my day... thanks a lot!!!


Monday, September 15, 2014 - 10:02:50 AM - Vijred Back To Top (34532)

Very Useful... Thank you!


Tuesday, March 18, 2014 - 1:35:03 PM - Bala Back To Top (29801)

I appreciate your help, I was looking this similar query to find out the restore  history


Wednesday, October 30, 2013 - 4:46:04 PM - SQLDBA Back To Top (27342)

Hi,

What difference between file and database type restore in mssql ?


Tuesday, September 24, 2013 - 7:40:44 AM - Ahish RV Back To Top (26911)

Good post, i was looking for this script since i wanted to check when was the last time i restored and most importantly from which backup path, since i had taken lot of backups of the same DB :)

 

thanks


Friday, October 12, 2012 - 12:35:09 PM - P Back To Top (19896)

Thank you. Wors perfectly.


Monday, July 23, 2012 - 6:57:00 PM - Maddy Back To Top (18756)

Thanks Your script works.


Wednesday, May 02, 2012 - 2:32:02 PM - amir Back To Top (17238)

Very nice snippet. Thanks a lot.


Thursday, January 07, 2010 - 2:24:38 PM - phillips_jim Back To Top (4680)

How would you determine when the restore completed and then calculate how long the restore took?  I do not see any columns that might provide the completion time and date of the restore in the msdb.dbo.restorehistory table or any other restore related table.  Is this information available in any of the dictionary tables dbo, sys or INFORMATION_SCHEMA tables or views of Master or MSDB?



download





Recommended Reading

Identify when a SQL Server database was restored, the source and backup date

How to migrate a SQL Server database to a lower version

SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Make Network Path Visible For SQL Server Backup and Restore in SSMS

SQL Server Database Stuck in Restoring State








get free sql tips
agree to terms


Learn more about SQL Server tools