This article is about a script to provide database restore report for all or selective databases.
How many of you got requests to restore database from one environment to other? Yeah, I know it is one of those common activities in day to day life of a DBA. If I ask how many of you had to provide a restore report of a single database? There will be many of you have an assertive answer.
But one of the not so common request is to provide restore report of all databases. Here I am going to provide the queries to pull restore report of all and selective databases. First of all lets see the restore report of all the databases.
Furthermore the result set is more useful if you get the following fields: Source Instance, Destination Instance, Source Database Name, Destination Database Name, Restore Type, Restore Date, the login id/user name who performed the restore and the backup file name used in the restore.
Note: I have commented out few fields in the query which are not so important in all situations. You may need to uncomment them as per your requirement.
Query to provide database restore report of all databases
SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance, mb.database_name AS SourceDatabaseName, rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate, CASE WHEN rh.restore_type='D' THEN 'FULL' WHEN rh.restore_type='L' THEN 'Log' WHEN rh.restore_type='I' THEN 'Differential' END AS RestoreType, --sd.collation_name, --sd.compatibility_level, --sd.recovery_model_desc, rh.[user_name], bf.physical_device_name AS BackupFileName FROM msdb..[restorehistory] rh INNER JOIN master.sys.databases sd ON rh.destination_database_name = sd.name INNER JOIN msdb..backupset mb ON rh.backup_set_id=mb.backup_set_id INNER JOIN msdb..backupmediafamily bf ON mb.backup_set_id=bf.media_set_id ORDER BY restore_date DESC
The sample result set:
Most noteworthy field in the above result set is backup file name. The complete path with the file name shown is the location where backup was taken and not the path from where the restore files was picked. In other words if you take a backup on X location and then copy the backup file to Y location and restore process picked the file from Y location the result set will show the X location. Hope I am clear enough.
Query to provide database restore report of single or few databases
SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance,
mb.database_name AS SourceDatabaseName,
rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate,
CASE WHEN rh.restore_type='D' THEN 'FULL'
WHEN rh.restore_type='L' THEN 'Log'
WHEN rh.restore_type='I' THEN 'Differential'
END AS RestoreType,
--sd.collation_name,
--sd.compatibility_level,
--sd.recovery_model_desc,
rh.[user_name], bf.physical_device_name AS BackupFileName
FROM msdb..[restorehistory] rh
INNER JOIN master.sys.databases sd
ON rh.destination_database_name = sd.name
INNER JOIN msdb..backupset mb
ON rh.backup_set_id=mb.backup_set_id
INNER JOIN msdb..backupmediafamily bf
ON mb.backup_set_id=bf.media_set_id
WHERE rh.destination_database_name IN ('Test_4','Test_restore') --Put the database names
ORDER BY restore_date DESC
Result Set:
Hope this is helpful. Please feel free to let me know your thoughts by leaving your comments below.
You may like the below articles as well:
Script to List All Users with db_owner role in SQL 2000
List all permissions for a user in all or selective databases
Script to Rename SQL Agent Jobs
Script to find the SQL Agent Job Name in Execution
List of Users with db_owner Role in All Databases
