Working in a small environment where people have their fingers in many pies can sometimes be a pain.
The developers have full db_owner rights to the databases and can restore the DB to a previous version, this script helped my find who restored what, when they restored it and what backup / server was used as the source database.
Not that I like pointing fingers at who broke something, but could come in handy if someone rolled back the DB and something went missing.
SELECT DatabaseRestoredTo = RH.destination_database_name, TimeOfRestore = RH.restore_date, UserImplimentingRestore = RH.user_name, RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore' WHEN 'F' THEN 'File Restore' WHEN 'G' THEN 'Filegroup Restore' WHEN 'I' THEN 'Differential Restore' WHEN 'L' THEN 'Log Restore' WHEN 'V' THEN 'Verify Only' END, ServerWhereBackupTaken = BS.server_name, UserWhoBackedUpTheDatabase = BS.user_name, BackupOfDatabase = BS.database_name, DateOfBackup = BS.backup_start_date, RestoredFromPath = BMF.physical_device_name FROM msdb.dbo.restorehistory RH INNER JOIN msdb.dbo.backupset BS ON RH.backup_set_id = BS.backup_set_id INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id ORDER BY RH.restore_history_id
