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.

01

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

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