The other day we were chatting about DR scenarios and one of the scenarios that cropped up was what to do when Master becomes corrupt.
It doesn’t take alot of searching on Google to find some good blog posts with step by step guides on how to either restore master or Rebuild, but I hadn’t come across a post where master was so screwed that the SQL service wouldn’t start at all.
So cue DR scenario number one…
- A Second SQL server instance running the same SQL version
- A Recent backup of the master database
The first thing I did was stop the SQL service for the Instance that I wanted to break, once stopped this gave me access to mess with the master mdf file:
Once I removed a portion of the contents from the mdf and saved the file I attempted to start the SQL service:
At this stage I decided to try some methods that I read online, I added -m as a startup parameter and attempted to start the SQL service , the service will not start and strangely enough I didn’t see anything in Event viewer either but after a couple of attempts I gave up and started scratching my head.
I had an Idea..
What if we could restore master as a different name on another SQL Server instance running the same version, Offline the restored master database , nab the files and copy them to the problematic Instance and rename the files?
I know its sounds completely nuts doesn’t it, but in this scenario I felt that there was nothing to lose, why not try it out!
Below is the restore from a good backup of master, restored to a different SQL server instance (SQL01) the instance we have an issue with is SQL02.
Then I Offline the database…
Locate the files in the file system and copy them to a share location where SQL02 has access.
These are my copies:
I rename the files:
I decided it would be a good idea to keep the old files so I took a copy of them and placed them in this share too.
I don’t have a screenshot for this step but all i did was copy the files to the correct locations on SQL02, both the mdf and the ldf are now copied to the problematic server , so what happens when we start the SQL service…
The SQL instance is back, up and running with a recent copy of master 🙂
I didn’t even know this method was possible with a System database!
I am not saying that this method should be adopted as your go to, there are far better methods to adopt as a go to method, but if you do find yourself stuck with a SQL service that just will not start despite the use of startup parameters and you know that Master is the issue then why not give it a try.
Here are a couple of good blog posts that I came across:
Thanks for reading.