March Madness – SQL Server System Tables – restorehistory

This is the last post in my series on SQL Server system tables. I have enjoyed writing these posts for the past 19 days and I might be persuaded into doing something similar in the future but only if I can find a way to make it more unique than just following a calendar.

If you have been following this series for the past three weeks you may have noticed that I have tried to build upon each post a little bit from one day to the next. For example on the first day I started with syslogins and then moved to sysusers, explaining how some system tables were scoped at the instance level and others at the database level. There was not always a straight line between one day to the next, but I drew a line where I thought they were needed.

Well, since yesterday we talked about backups, that means today we will talk about the restores. In case you were not aware of this fact, as a DBA your number one job is to be able to recover data. If you can’t do that then you might as well look for a new profession because you will not last long in your current job.

So, without further delay, today we look at the msdb..restorehistory table.

What is this table for?

This table will contain one row for each successful restore operation. But don’t just take my word for it, go ahead and see for yourself. If you did a backup using the code snippet from yesterday’s post, go ahead and do a restore with the following:

SELECT COUNT(*)
FROM msdb..restorehistory
GO

RESTORE DATABASE [AdventureWorks]
FROM  DISK = N'C:\AdventureWorks_1.bak', 
DISK = N'C:\AdventureWorks_2.bak'
GO

SELECT COUNT(*)
FROM msdb..restorehistory
GO

So, a restored from a striped set will only count as one restore, similar to how striping a backup is only one row added to the backupset table. But what about doing a log restore? Or how about using the WITH NORECOVERY option? What happens then? Well, let’s find out! Substitute the following code for the previous example:

RESTORE DATABASE [AdventureWorks]
FROM  DISK = N'C:\AdventureWorks_1.bak', 
DISK = N'C:\AdventureWorks_2.bak'
WITH NORECOVERY
GO

There you go, use of NORECOVERY does not affect the number of rows added to the msdb..restorehistory table. Every successful restore operation will get logged to this table. And if you want to know what type of restore operation then you only need to look at the restore_type column which has the following values:

D = Database
F = File
G = Filegroup
I = Differential
L = Log
V = Verifyonly
R = Revert

This information can be very helpful, read on to find out when and where.

Why should you care?

Because as a DBA you need to know such details. Has the following story ever happened to you?

The offshore team restores a database. Developer calls lead DBA to state that the restore was done incorrectly and questions the skill level of the offshore team. DBA asks “How do you know it is wrong”? Developer says “Because the data I want isn’t there.” DBA says OK, spend 10 minutes writing a script, gets the results from the script that prove the offshore team did exactly as they were asked, and emails the developer. Developer call back 10 minutes after that to say “We requested the wrong restore time, can you have them restore from an earlier time?” DBA says yes.

So, yeah, more than anything else I needed to use the restorehistory table simply to prove that a database was restored to the exact point at which it was requested to be restored from. You can read more about that here, or just run this code for yourself:

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From],
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO

The above code will return details regarding the last time a database was restored.

Where else is this information?

The msdb database has all the details on restores, as well as backups. Poke around a bit more to see what other gems you can uncover.

Thanks for reading the series!