SQL Server Database Restore History


While working with databases, one of the most repeated task are database restore and refresh from production to test or dev environment. I’ve had situations where after a refresh (restore databases), our client would assume the refresh was not done or the refresh was not completed. They were not seeing the data they were expecting. Now how can you prove that you did the refresh from the most recent backup successfully?

The below script will give you all restore details like restore date, which backup was restored, what sources location, and etc.


use Master

SELECT [rs].[destination_database_name],

[rs].[restore_date], [bs].[backup_start_date],

[bs].[backup_finish_date], [bs].[database_name] as [source_database_name],

[bmf].[physical_device_name] as [backup_file_used_for_restore]

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]

ORDER BY [rs].[restore_date] DESC



-By MD Ullah

One thought on “SQL Server Database Restore History

  1. This is one of the best script I have come across. This made my life so much easier to find out when was the last time we did the restore and if it completed successfully!

    Great Work!




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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s