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
setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=”sreekanthPC\sreekanth” /SAPWD=”Pa$$w0rd”
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.”
very useful info…
@SRK – Glad you learned something new 🙂
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 🙂
can be just restore the master database from the backups, do we need to reailly rebuild the master database?
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
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.
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.
It is very helpful….
Thank you very much for your article
It was very helpful for and much appreciated
Many thanks for your article, it helped me a lot.
Worked like a champ!!! And thank you for showing the error with the path. I would have spent a lot of time researching it!!!!
Good Job srikanth. Very helpful in case we don’t have system database backups.
while executing below one.
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2>setup
/ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER [/SAPWD=india@123]
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
So, what does your summary.txt file say?
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”
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
CREATE DATABASE [SalesDB] ON
( FILENAME = N’C:\SalesDB.mdf’ ),
( FILENAME = N’C:\SalesDB_log.ldf’ )
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
setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=”PC\Administrator” /SAPWD=”Pa$$w0rd”
the tried this
setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=PC\Administrator /SAPWD=Pa$$w0rd
SUCCESS….. once master database rebuild, we proceed with restoration from backup.
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.
Just a little tip for people using sql 2012, use the /110/ folder instead of the 100.
Thanks a lot. You made my day 😉