How to Rebuild Master Database?? AKA Rebuilding SQL Server 2008R2.

Have you ever been in a situation where you lost the complete Drive where your System Databases are residing and even SAN Admins were not able 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 System Databases won’t work because 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 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 mandatory to rebuild your Master. Those days are gone..That was the case with SQL Server 2005 and earlier. Starting SQL Server 2008 it’ll rebuild based upon the TEMPLATES which 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 which SQL Server created while initial Installation of this Instance on my machine.

These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below

Note: On My laptop I Installed every thing Just on C Drive. You should never ever do this in a real environment. There’s no single valid/good reason to do so…

Once rebuild process is complete, You’ve 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 etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and 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 post, let’s try to rebuild our entire SQL Server/Master Database.

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

Step1: Now my Data folder looks as shown below

Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).

In the Event Viewer I’m seeing the below

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 mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know 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 to maintaining all these important details individually at 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 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


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

In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).

Very Very Important: Actually It failed for me and got below 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( may be may be 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 make use of setup.exe from Installation media. This should work. I hope this strange behavior has been fixed in DENALI.

Sooo…..the actual Syntax and Path which worked for my case is as shown below

it took around 7-10 minutes in my case and got a command prompt without any errors as you can see in the above screenshot 🙂

FYI Failed path(in my case) as per 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 achieved?? Did SQL Server Instance “DR” got rebuilt??  Yesssssssssss 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…. Tadaaaaaaaaaaaaaaaaaaaaaaa!! We did it guys 🙂 🙂


Now it’t time to (patch if applicable) and  restore your Databases from your healthy Backups accordingly. You can see how to restore System Databases here.

That’s it Guys!!! You should be aware of this process and these challenges and work arounds as a production DBA. I would recommend to test this in your lab at least once or twice before actual disaster strikes your Production Servers…(I hope you never need this….But you should not be surprised when it hits). Hope this helps. Cheers!


22 thoughts on “How to Rebuild Master Database?? AKA Rebuilding SQL Server 2008R2.”

  1. Great article, thank you. It didn’t actually work for me as tempdb was not recreated, but I’m thinking it’s something I must have done in my attempt to recover before finding your article. SO, I’m going to deliberately break my next install and try again 🙂

  2. whats the scenario where instance is up and running and master needs restoring is it lets say u deleted the master data or log file if yes what happend to instance won’t it go down for the need of master database lets say to be intact in that case we still need to rebuild the database right

  3. Looking at my own installations, it looks like the setup.exe in the ‘release’ folder is the initial installed version. If you have servicepacks installed, you will find the most recent version of setup.exe in the ‘SQLServer2008R2’ folder. Does that make sense? There’s two versions of setup.exe in my ‘Update cache’, the folders under ‘Update cache’ corresponding the with SP1 and SP2 KB-numbers issues by Microsoft. The date of the setup.exe in the most recent installed SP2 seems to correspond with the date of the setup.exe in ‘SQLServer2008R2’ folder.

  4. You are my SQL HERO! Thanks to your tip, I restored after a failed upgrade and then attached all my mdf files. Thank you so much for taking the time to post.

  5. Worked like a champ!!! And thank you for showing the error with the path. I would have spent a lot of time researching it!!!!

  6. while executing below one.

    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2>setup

    am getting below error what to do , please suggest

    The following error occurred:
    The state of your SQL Server installation was not changed after the setup execut
    ion. Please review the summary.txt logs for further details.

    Error result: -2068643838
    Result facility code: 1203
    Result error code: 2

    Please review the summary.txt log for further details

  7. Running it from ….\Microsoft SQL Server\100\Setup Bootstrap\Release
    the first outputline says “Microsoft (R) SQL Server 2008 Setup 10.00.****”

    Running it from …….\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2
    the first outputline says “Microsoft (R) SQL Server 2008 R2 Setup 10.50.****”

    IF trying to rebuild a instance that was installed as an R2, 2008 Setup 10.00 isn’t correct. 2008R2 Setup 10.50 is…

    Just wished the errormessage was a little bit clearer. Would have saved me a lot of “googletime”

  8. If you want to attached your required database them you have to configure your contained database authentication

    sp_configure ‘contained database authentication’, 1;

    — To Attached your database get path and replace below

    USE [master]
    ( FILENAME = N’C:\SalesDB.mdf’ ),
    ( FILENAME = N’C:\SalesDB_log.ldf’ )


  9. I am very thankful you made this in detail.. We encountered similar issue and was able to resolved it with a little maneuver:

    1. We used setup.exe from both path – Failed.

    2. Then we used setup.exe which comes with the CD installer

    3. We used below command – Failed


    the tried this


    SUCCESS….. once master database rebuild, we proceed with restoration from backup.

  10. In our case the system DB files in the Template folder did not exist as we use netbackup to do the system state backups. We have a NB policy that excludes .mdf and .ldf files. The Rebuild failed when we tested the recovery of a node that was rebuilt from the system backup. This process had failed when we ran the rebuild command.

    1. We had to copy the system DB files from another SQL Server (similar version/SP) to the template folder
    2. Ran the Rebuild command and it executed successfully
    3. Moving forward we will copy the system DB files from the Template folder to our workspace on the server and rename them (.mdf to .fdm, .ldf to .fdl) so they are backed up by netbackup. In case of Node recovery when we rebuild the database we can rename these files and copy them to the Template folder.
    4. Restored the system database from the recent backup and we were up and running.

Leave a Reply to sreekanth bandarla Cancel reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: