When was the last time your SQL Server database was restored
April 13th 2009 14:37
When was the last time your SQL Server database was restored
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:
Database RestoredBy RestoreType RestoreStarted RestoredFrom RestoredTo
AdventureWorks sa Database 2009-04-06 G:\Program... E:\Program...
AdventureWorks sa Database 2009-04-06 G:\Program... F:\Program...
Here is the definition of each of the result set columns.
ColumnName 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.
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 = '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:
Database RestoredBy RestoreType RestoreStarted RestoredFrom RestoredTo
AdventureWorks sa Database 2009-04-06 G:\Program... E:\Program...
AdventureWorks sa Database 2009-04-06 G:\Program... F:\Program...
Here is the definition of each of the result set columns.
ColumnName 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.
| 27 |
| Vote |
Add To: 
