Script for tracking of DB restoration activities

Description:
Following MSSQL script/Query would help us to find who restored Database, when they restored it and what backup/server was used as the source database. So we can track DB restoration activities.

Script

SELECT<br />
DatabaseRestoredTo = RH.destination_database_name,<br />
TimeOfRestore = RH.restore_date,<br />
UserImplimentingRestore = RH.user_name,<br />
RestoreType = CASE RH.restore_type<br />
WHEN 'D' THEN 'Full DB Restore'<br />
WHEN 'F' THEN 'File Restore'<br />
WHEN 'G' THEN 'Filegroup Restore'<br />
WHEN 'I' THEN 'Differential Restore'<br />
WHEN 'L' THEN 'Log Restore'<br />
WHEN 'V' THEN 'Verify Only'<br />
END,<br />
ServerWhereBackupTaken = BS.server_name,<br />
UserWhoBackedUpTheDatabase = BS.user_name,<br />
BackupOfDatabase = BS.database_name,<br />
DateOfBackup = BS.backup_start_date,<br />
RestoredFromPath = BMF.physical_device_name<br />
FROM<br />
msdb.dbo.restorehistory RH<br />
INNER JOIN<br />
msdb.dbo.backupset BS<br />
ON<br />
RH.backup_set_id = BS.backup_set_id<br />
INNER JOIN<br />
msdb.dbo.backupmediafamily BMF<br />
ON<br />
BS.media_set_id = BMF.media_set_id<br />
ORDER BY<br />
RH.restore_history_id

For example see following screenshot

Script for tracking of DB restoration activities

 

 


<<Click here to see all posts>>

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.