October 14, 2020 at 4:00 pm
We recently had a major SAN failure and completely lost many of our SQL Server instances. For many instances, we had no user database backups (this was by design due to storage limitations). However, we have system databases for all of the lost instances. I want to recover a list of the databases originally hosted on each failed server.
I was hoping to restore the various master database backups to an existing SQL Server and just query the list of databases. For example, if I restored master.bak from a lost instance to a database called restore_master, i was hoping to query restore_master.sys.databases or restore_master.sys.master_data_files. However, this doesn't work as these system wide views only report what is running on the current server.
I know I can install SQL Server instances and restore the master databases to these newly installed instances, but that is going to be a very large task as we have a wide variety of SQL Server versions (including a variety of specific CUs). So I'm looking for a shortcut. Is there any way I can restore a master database backup as a "regular database" and somehow query it to find what databases it originally had?
October 14, 2020 at 6:37 pm
I don't think you can easily. Lots of stuff in here is a view, which means there are live queries here.
What I'd do, is restore this master as the master on a new instance, replacing the master that's there. Then you should get a number of dbs that come up as suspect, because files might not be available. Then try to sort out what dbs are supposed to be here.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 14, 2020 at 8:47 pm
From a few others:
If you connect to the restored instance with the DAC, and then go to the restored_master, you can try this:
select * from sys.sysdbreg
That gets names, but I don't think you can get files. You could also try DBCC Page, to get the raw data from the system table. Sysschobjs I think it was..
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 14, 2020 at 9:11 pm
One other question, you have the systemdbs, but do you have the versions of SQL for each?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 14, 2020 at 9:25 pm
If you restore headeronly for the master backup, this should give you the versions. You can map those to the CUs from here: https://qa.sqlservercentral.com/articles/sql-server-build-lists
If the master dbs are in folders, you can likely automate some of this from PoSh and SQL to get a list of versions for files. I might write back the version to the same folder in a text file so you know.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 14, 2020 at 9:54 pm
Hi Steve -- thanks so much for the assistance.
You are correct, I could get the version from the master backup, install SQL instances and get them up to the specific version and then restore the master database but that's way more work than I wanted to do, given the variety of versions.
Fortunately, it looks like your recommendation to restore the master as something else and then query sys.sysdbreg from within the DAC does indeed get me the database names! These are non-prod database servers so as long as I can show users what they are missing, they can decide what's most important to them. That's primarily what I was after.
In addition to names, I was hoping to also get the db sizes so I could accurately estimate for the SAN admin the disk space each instance will need, but I don't see that info in sysdbreg. Got any ideas there? If not, no problem, this has been a HUGE help and I can estimate sizes another way. Take care.
October 14, 2020 at 10:53 pm
Sizes are likely out because the file list doesn't seem available.
You inspired me. I'm going to write an article based on this, as some of these things are interesting from a DR standpoint.
Hope you get things solved here quickly and easily.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 15, 2020 at 6:42 pm
You inspired me. I'm going to write an article based on this, as some of these things are interesting from a DR standpoint.
The very first recommendation in such an article should be that user database must be backed up and, if you don't have the space somewhere to do so, you really need to spend the money to buy some. Every one in the world knows that spreadsheets, MS Access, and non-production databases are the things actually responsible for the success of a company, right? D
Second thing is, if your database files don't live on two separate machines, hopefully in two different builds or, at the very least, on opposite ends of the server room. The same goes for recent online backups... It's ok for them to live on less expansive NAS instead of SAN but, no matter what, they MUST NOT live on the same box(es) as the data, PERIOD!
The bottom line is, don't find out the hard way that all that supposedly spurious user data actually IS valuable and essential. I've seen this happen several times now and some companies can eventually recover from such a mistake (sometimes taking several months and a lot of people power to regenerate what was lost) and some can't and I've seen some of those companies go out of business because of it.
That also still applies to people using the cloud because a lot of people still store stuff locally and no matter how impossible it may seem to lose all of your production data on the cloud, would you want to bet your company on someone else's supposed attention to detail, hardware, and procedures?
If it's data, protect it until you can prove that it's actually and serious temporary in nature and can actually be regenerated in a pinch or is actually worthless.
--Jeff Moden
October 19, 2020 at 1:19 pm
Steve Jones - SSC Editor wrote:You inspired me. I'm going to write an article based on this, as some of these things are interesting from a DR standpoint.
The very first recommendation in such an article should be that user database must be backed up and, if you don't have the space somewhere to do so, you really need to spend the money to buy some. Every one in the world knows that spreadsheets, MS Access, and non-production databases are the things actually responsible for the success of a company, right? D
Yes. If nothing else, buy a 2 TB External drive for $99 and backup non-critical DB there once a week.
November 10, 2020 at 3:31 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply