Restoring Master – When All else fails and the SQL service won’t start.

lady-1721678_1280

 

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…

PreRequisites:

  • 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:

2017-07-30 21_09_26-SQL02 [Running] - Oracle VM VirtualBox
Lets open the Master mdf file and mash it up a little…
2017-07-30 21_09_54-SQL02 [Running] - Oracle VM VirtualBox
Lets delete this portion of text and Save.
 

 

Once I removed a portion of the contents from the mdf and saved the file I attempted to start the SQL service:

 

 

Uh oh!

 

2017-07-30 21_11_04-SQL02 [Running] - Oracle VM VirtualBox
Event viewer showing the reason for the Service unable to start

 

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.

 

2017-07-30 21_14_23-NVIDIA GeForce Overlay
Restore from the last backup of Master

 

 

Then I Offline the database…

 

2017-07-30 21_15_22-DC01 [Running] - Oracle VM VirtualBox
Offline the restored copy of Master

 

Locate the files in the file system and copy them to a share location where SQL02 has access.

 

2017-07-30 21_15_59-SQL01 [Running] - Oracle VM VirtualBox
Locate the files and make a copy to a locate that our other SQL node can access

 

These are my copies:

 

2017-07-30 21_17_08-SQL01 [Running] - Oracle VM VirtualBox
Here is my copy of the files

 

I rename the files:

 

 

2017-07-30 21_17_58-SQL02 [Running] - Oracle VM VirtualBox
Rename the files to the correct filenames
2017-07-30 21_18_37-SQL02 [Running] - Oracle VM VirtualBox
Renamed 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.

 

2017-07-30 21_19_22-SQL02 [Running] - Oracle VM VirtualBox
I have renamed the old ones and copied to the same location for safe keeping.

 

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…

 

 

2017-07-30 21_20_13-NVIDIA GeForce Overlay
Start the service
2017-07-30 21_20_26-NVIDIA GeForce Overlay
Service is running

 

It’s Alive!

 

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:

Thomas LaRock – HOW TO: RESTORE THE MASTER DATABASE IN SQL SERVER 2012

John Grover – Restoring the SQL Server Master Database Even Without a Backup

Thanks for reading.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: