Skip to content

How to get my SQL Server Database Restore history? Or when was my SQL Server database last restored?

February 26, 2013

There can be situation when your developer calls and say he doesn’t see production data restored to lower environments although a database restore to lower environments is scheduled to happen every night. How to check when was the last time database(s) was restored? You can use below script to get the info.

1: Script to get restore history of database(s). With a little modification as indicated you can get below details as well:

  • Restore History of only one/all database(s).
  • Restore history of one/ all the database(s) between or on a particular date.
  • When was last time database restored from full/diff/log backup.

SELECT destination_database_name AS 'Database Name',
restore_date AS 'Date Restored',
CASE restore_type
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Diffrential backup'
WHEN 'L' THEN 'Log Backup'
END AS 'Restored From',
CASE recovery
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS 'With Recovery',
backup_finish_date AS 'Backup taken',
Cast(backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)' ,
physical_device_name AS 'Backup Location'
FROM msdb..restorehistory,
msdb..backupset,
msdb..backupmediafamily
WHERE msdb..restorehistory.backup_set_id= msdb..backupset.backup_set_id
AND msdb..backupset.media_set_id=msdb..backupmediafamily.media_set_id

-- Uncomment below line and Replace <Database name> with DB you want to check backup history
--AND database_name='Db_name'

-- Uncomment below line and replace start and end dates with dates you want to check history
--AND restore_date>='2013-01-29 00:00:00' And  restore_date<='2013-01-29 23:59:59'

--Uncomment below line to see only the restore from full backups, replace with 'I' to check diffrential and 'L' to check only Log backups.
--And restore_type ='D'

ORDER BY destination_database_name,
restore_date DESC 
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: