July 27, 2016 by Kenneth Fisher
I frequently need to know where backups went and I restore those backups for operational recovery on a regular basis. Would you believe in 20+ years as a DBA I can count the number of database restores for a disaster on my fingers? (Which is good because taking off your shoes at the office is considered bad form.) In that same 20+ years I’ve needed to know when a database was last restored exactly once. Still, it’s always nice to know where information is. In this particular case rather than checking BOL I queried msdb.sys.all_objects for anything like ‘%restore%’ and turned up dbo.restorehistory.
SELECT * FROM dbo.restorehistory
Category: Backups, Microsoft SQL Server, SQLServerPedia Syndication
| Tags: backups, microsoft sql server, restores
I have a script which will display the Restore History for databases. It helps if the Backup Comment includes enough details about the original backup to enable the file to be identified, but failing that the original database and server names, and the backup start date/time, help.
Nice! I love creating scripts like that. I’ll have to add that one to my bag of tricks.
[…] on a SQL Server. You can even specify -Since if you’d like to filter by date. Thanks to Kenneth Fisher for the […]
[…] getting backup history earlier but now we talk about Get-DbaRestoreHistory a command inspired by Kenneth Fishers blog post to show when a database was restored and which file was […]
[…] for what LOADHISTORY actually does? It causes you to write an entry to the restore history table. You can tell which record this is because the restore_type is set to a V. Really, the only […]
[…] weren’t correctly re-applied afterwards. Hard to prove but easy to disprove by looking at the restore history. Hopefully you scripted out the permissions ahead of time or at least took a backup before doing […]