Rebuilding SQL Server 2008 R2

How to Rebuild Master Database

Have you ever been in a situation where you lost the complete Drive of where your System Databases are residing and even SAN Admins were unable to bring the Drive back? Fortunately I've never been in that situation. :) What would be our available options if that disaster strikes? In this scenario, restoring the System Databases won't work because the SQL Server Service itself is completely down and your System Databases mdf and ldf files are gone! So what should we do? REBUILD YOUR MASTER. Rebuilding the Master will actually create brand new System databases from scratch as if you just Installed your SQL Server. There's a misconception that we need the Actual SQL Server media and it's necessary to rebuild your Master. Those days are gone. That was the case with SQL Server 2005 and earlier. Starting with SQL Server 2008 it'll rebuild based upon the TEMPLATES that it creates in your BINN Directory. (This is the one of the main reasons, you should place your SQL Binaries and actual System Databases on completely separate Physical Drives. Let me show you what I'm talking about. You can see the templates folder that SQL Server created during the initial Installation of this instance on my machine.



These are not the actual system database files. I repeat, these are just templates that SQL Server will create behind the scenes during the installation process. My actual System Databases are located in the DATA folder as shown below.



Note

On my laptop I Installed everything, just on the C Drive. You should never ever do this in a real environment. There's no single valid/good reason to do so.

Once the rebuild process is complete, you need to restore all the user databases from your backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects and so on. Yes! I agree that sounds like a very painful process. But if you have all your recent healthy backups and a few other dependent objects, you are the champion. :) So, if you don't have a proper Backup/Restore Strategy in place, you are gone! You might even loose your job for this single most important valid reason. In this article, let's try to rebuild our entire SQL Server/Master Database.

I'll simulate the scenario by shutting down SQL Server Services on this instance and physically deleting the mdf and ldf files of the system databases. Let's see how it looks and what are the errors you'll be getting in this case.

Step 1

Now my Data folder looks as shown below.



Step 2

I tried to start the SQL Server Service from our Configuration Manager and the following are the error messages that I'm getting (see the following Screenshots).



In the Event Viewer I'm seeing the following:



Okay! In this case we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.

Step 3

Let's see how to rebuild using SETUP.EXE.

As I already said above we no longer need the installation media to rebuild SQL Server (you can use it as well if you want to). All you need to understand is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. (Standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain of maintaining all these important details individually at the Server level). In my case it's “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below.



Step 4: Open a Command Prompt (Run as Admin) and navigate to this folder where you can find SETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In my case the syntax was:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=”sreekanthPC\sreekanth” /SAPWD=”Pa$$w0rd”

If you want to explore all the other options and parameters available to us then you can issue “setup.exe /?” for help from Windows as shown below.



In the preceding syntax every switch is seperated by a space. Here we are using Quiet Mode and the action is Rebuild master Database, the instance name is DR (for the default instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing the sa password (since mine is a mixed mode).

Very Very Important: Actually It failed for me and I got the following error Messages! The error Messages were “The following is an exception stack listing the exceptions in outermost to innermost order Inner exceptions are being indented Exception type:

Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.”

I've no idea what it is trying to convey here, looks like a bug (maybe, maybe not) to me in SQL Server 2008R2 (I'm using 2008R2 for this demo).

Work Around: I've selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe“

Even if this doesn't work for you, please try to use setup.exe from the installation media. This should work. I hope this strange behavior has been fixed in DENALI.

So the actual syntax and path that worked for my case is as shown below.



It took around 7-10 minutes in my case and I got a command prompt without any errors as you can see in the preceding screenshot.

FYI Failed path (in my case) as per the Microsoft documentation is as shown below.



Please keep this bug in your mind and don't just panic if you encounter the same error message.

Well, with all this hard work, what did we achieve? Did the SQL Server Instance “DR” get rebuilt? Yes as you can see below, all the System database files are back.



Also, I was able to Start SQL Services from the Configuration manager and as you can see below, .everything appears to be brand new. Tada! We did it guys.