Recovering Databases From a Master Backup
-
Comments posted to this topic are about the item Recovering Databases From a Master Backup
-
I remember reading the thread you spoke of and the great job you did in helping the OP figure things out. Nicely done. And nice followup article.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was a real scenario I faced a couple years ago. I had a client who lost a very large SAN. And to make matters worse, the DBA was on leave and there was no build documentation...not even much knowledge of SQL versions etc. Thankfully, the SAN backups existed and were viable.
My strategy was to start with the standard SQL Server "errorlog" files. Using these you not only get the version information but a lot of other useful information you will need when installing a new instance (e.g. collation, service account) and it also gives you a list of the databases you need to deal with. For me, that got me pretty close since I was going to do database restores from and did not need to worry about each file.
And the happy ending to all of this was...they SAN vendor flew in an engineer with replacement parts and actually got the SAN back up by the time I was getting ready to re-install SQL and restore databases. A much happier outcome for what was looking like a very long night
-
Hi Steve
I think this is something every DBA needs to know about, so thanks for sharing!
I have a small note though... I have restored master a few times on an instance on a VM on my laptop just to get some info about for example how a linked server was configured when someone thought it had changed. You are correct (of course you are :)) that the version needs to be exactly the same as the version where the backup was taken if you want to replace master.
However, I also wrote some code years ago to automatically verify backups. It selects a random instance and a random database, restores it (as dummydb), runs an integrity check, then mails us the result. Sometimes that random database happens to be master. The integrity check fails (internal tables and so) but the restore succeeds, on a different version. So I'm curious why that didn't work for you. To be sure, I tested it manually, restoring from 2014 and 2017 to 2019 (screen shots added - version numbers from/to are visible). Both succeeded. So the article you link to might no longer be valid. I do remember seeing that error message from your screen shot with the auto-restore checks as well, but I can't remember what exactly cause it or what I did to solve it (might have been an upgrade or edition change).
Screen shot successful restore from 2014 to 2019
Screen shot successful restore from 2017 to 2019
Maybe someone reading this came across this as well and has a better memory than me 🙂
Kind regards
Thierry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply