Last Restore of a Database – Back to Basics

Remember When…

Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s hope this post holds up to the intent of the challenge.

With this being another installment in a monthly series, here is a link to review the other posts in the series – back to basics. Reviewing that link, you can probably tell I am a bit behind in the monthly series.

Database Restore

As a data professional, one should be somewhat cognitive of backups with regards to the data. From the point of the creation of the backup, we can go in one of two directions with this article. Due to the importance of each of the directions, I will devote a basics article to each.

The order of publication of these articles in no means denotes a level of priority to the topic. I feel each is equally important. The two directions I see both deal with the restore of that backup that was created. One direction is in regards to reporting and the other direction is the actual restore.

Last Restore Date

Something that I seem to be getting asked more and more frequently is how to determine when a database was last restored. Maybe it is just a hot topic and maybe it will be short lived, but it sure seems to be something that is on the tip of peoples minds of late. So, how does one determine the last time a database was restored? The answer is actually quite simple. Within the msdb database, there is a table (that maintains the history of database restores) called restorehistory.

The restorehistory table stores information that will help you better understand what kind of restore was performed, who did the restore, when it was done, and even some information about the backup that was used to perform the restore. You can read more about the specifics of this table from the msdn article – here.

A quick means to query this table is via the following script:

SELECT r.restore_date
, r.destination_database_name
, CASE r.restore_type
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'FileGroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'V' THEN 'VerifyOnly'
END AS restore_type
, r.replace
, r.recovery
, r.user_name
FROM msdb.dbo.[restorehistory] r
WHERE r.destination_database_name = 'TestDB'
ORDER BY r.destination_database_name,r.restore_date DESC;

Now, that is an extremely simple query looking at my TestDB database. The results of that query on my system are as follows:

As is shown in the preceding illustration, I have multiple restores and restore types of this particular database. This is a good start. Let’s take this query up a notch. Not all production environments will have databases that are restored on the same server. What do we do to report on databases that are restored on the production server vs. the databases that exist on the production server? That is where this next query comes in to play.

DECLARE @DBName VARCHAR(128) = 'AdventureWorks2014' --NULL
, @MostRecentRestore TINYINT = 1 --NULL --1
, @RestoredDBsOnly TINYINT = NULL; --1
SELECT DatabaseName = [d].[name]
, [d].[create_date]
, [d].[compatibility_level]
, [d].[collation_name]
, r.restore_date
, r.user_name AS RestoredBy
, r.destination_database_name
, r.restore_type
, r.replace
, r.recovery
--, r.RowNum
FROM master.sys.databases d
LEFT OUTER JOIN ( SELECT r.restore_date
, r.destination_database_name
, CASE r.restore_type
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'FileGroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'V' THEN 'VerifyOnly'
END AS restore_type
, r.replace
, r.recovery
, r.user_name
, RowNum = ROW_NUMBER() OVER ( PARTITION BY r.[destination_database_name] ORDER BY r.restore_date DESC )
FROM msdb.dbo.[restorehistory] r
) r
ON d.name = r.[destination_database_name]
AND r.RowNum = ISNULL(@MostRecentRestore, r.RowNum)
WHERE 1 = 1
AND ISNULL(r.RowNum, -1) = COALESCE(@RestoredDBsOnly, r.RowNum, -1)
AND d.name = ISNULL(@DBName, d.name);

In this latter query, I have added a few things to help for various reporting needs. You never know when the CIO or an auditor is going to come to you with a request to filter the data differently for restored databases. In this vein, I added parameters that will help filter the results down to a specific database, return all databases, or return all databases that have a restore history on the server. In this case, I decided to use the AdventureWorks2014 database as my example database. You will want to change the parameter as needed. If you pass a null value to the database name, then all databases can be returned in the result set – depending on the values used for the remaining parameters.

Caveat

This is all fine and well if the databases are restored onto the same server as the source database, right? What about the case where the database is restored to a different server? Well, the query still works for reporting the restored databases on the server where the database is restored. The complexity comes when trying to correlate back to the original production server. My recommendation here is good documentation and an administrative linked server that is not accessible via the application accounts or any user but the DBA group.

Recap

I have provided two quick examples of how to retrieve the restore history for your databases. I recommend that the restore history be checked on a routine basis. You never know when an over-permissioned user may decide to restore a database for you and then have all of the rest of the users coming to you to determine what happened. This will also be essential when we routinely test our database backups. Stay tuned for the next article where I will explore that aspect of database restores.