I ran into a very interesting thread recently, where a posted had experienced a disaster. In this case, they had a SAN failure, where they lost a number of instances. There were backups of the master databases, but not backups of all the user databases. They wanted to try and determine which databases were on which instance, and which version of SQL Server to start the recovery process, trying to match up files to database and instances.

A true disaster. In fact, possibly an RGE, as Grant has named them. I hope this doesn't happen to anyone in a production environment, as you might be in trouble. Even in non-prod environments, I could see this being an issue. I recommend you a) make sure you have backups of dbs, and b) use some sort of monitoring or automated process that lets you know what your systems look like.

In this article, I want to look at how you can get some information from what is available to decide how to proceed with your recovery efforts. We'll get version information and then database details.

NOTE: You should have backups. If your data is important, make sure backups are set up. Don't let someone create a database without a backup, even a simple "full backup every night" basic script. This includes development, as development is your manufacturing line. These are important databases.

The Scenario

I'm going to simulate an issue with my existing system. I've got a SQL Server 2017 instance running with a number of databases. I'm going to take a backup of the master database first.

BACKUP DATABASE [master]
TO  DISK = N'D:\SQLBackup\master2017.bak'
WITH
    NOFORMAT
  , NOINIT
  , NAME = N'master-Full Database Backup'
  , SKIP
  , NOREWIND
  , NOUNLOAD
  , STATS = 10;
GO

Now that I've done this, I'll delete a few older test databases. This is to simulate the idea that things might change rapidly in a dev/test area. Maybe a dev has removed some databases, but not everyone is aware. In any case, we want to try to get the idea of what this instance looked like as of this backup.

Note: This is why you want and need regular backups of all databases.

What Version Do I Install?

This is an interesting item for me. After all, if I have a major crash and I am restoring master, am I sure what combination of SQL Server, Service Packs, and Cumulative Updates to install? For the restore process, usually the Version and SP are enough. However, for application behavior and query behavior, I should know what the CU level is at the time of the last backup.

Fortunately, I can get some of this from the backup of the master database. What's more, I can use a recent version of SQL Server to get this. On a SQL Server 2019 instance, I'll run this:

USE [master];
RESTORE HEADERONLY  
FROM DISK = N'D:\SQLBackup\master2017.bak'

This returns me a lot of information, including:

• instance name
• Backup date (start and finish)
• Software major, minor, and build numbers.

In this case, I see:

This corresponds to the installed instance:

This means that I can now use a build list to get the correct SQL Server version and patch level. In this case, I need to go to the 2017 build list where I see that 14.0.2027 corresponds to the GDR. Side note, I need to patch this instance.

With this info, I could install a new instance that matches this level and then restore the master database from this backup.

One note, I need to get the proper version of SQL Server, even if I want to restore master as a different database. For example, I tried to restore this backup (from 2017) on SQL Server 2019. I saw this:

In Object Explorer, I see this:

I found an old KB article that notes this. Even with a different name, you can't restore to the wrong version.

What Databases Exist?

The next question is what databases existed at the time of this backup? This helps me to prioritize the recovery operation, as well as allow me to start trying to map the files I have to this master restore and then attach those files. Remember, the SAN had issues, and while I have database files (mdf, ndf, ldf), I don't know which goes where or with what version.

In the incident, the files are available, but not the databases. We don't necessarily know what goes where. Likely there are some patterns that help, but changing db names or guessing might cause all sorts of apps to not work, or lots of griping from developers. Ideally, we want to recover things properly.

In my case, I restored the master database to a matching 2017 instance. In this case, I restored this database as Bob4. This is because I messed this up 3 times on the 2019 instance first. Once I restored this on the 2017 instance, I connected with the Dedicated Admin Connection. This allows me to query the underlying system tables in this database.

There are system base tables, on which the DMVs are based. DMVs are views, so we can't query these directly as they'll refer to the current instance. One of these is sys.sysdbreg. This contains info about databases, but isn't accessible from a regular connection.

However, if I connect with the DAC, I get query this table.

I can see that there are a number of databases here, including a few that are gone. If you look at the current state of this instance, the FSTest and sdfsdf databases are gone. However, now I have a list of dbs, and in the rest of the result set, there is the compat level and status.

With this info, I can now talk to business owners, get a list of databases that need to be recovered and start setting up instance and attaching files.

Summary

There is some information you can get from backups that can help you recover your instances and database to the proper places if you haven't correctly prepared. In this case, I did the following:

• Use the master backup to get the correct SQL version and patch level
• Restore this backup as a new name and then query for the database names

Of course, you ought to be backing up databases, even dev ones. This helps keep dev work moving. Set them in simple mode, and then run a single backup a night, but be prepared. Then you can get back to work.

For more advanced folks, you ought to keep code (and reference data) in a VCS and have a way to hydrate new databases quickly. Containers, restore from template, data virtualization, something that quickly builds a database and applies the latest stuff. Hopefully you'll get there at some point for all your enviroments.