In SQL Server 2000, we had a nifty little utility called rebuildm.exe which would rebuild the master database in emergency situations. This utility was removed in SQL Server 2005 and has not been seen since. So what do we do? We can use Setup.exe, but it’s not as straight forward as you would like….
If the master database is corrupt, you cannot even start SQL Server so even though you may have a good backup of the master , you cannot restore it with SQL down. In SQL Server 2000 and before, we used rebuildm.exe. This would create a “vanilla” version of master (and msdb and model) so you could at least get SQL started in single user mode, then you could restore your master backup and all would be well. This is something we had to practice. Plan for the worst and hope for the best.
However, in SQL Server 2005, rebuildm.exe was removed. Unfortunately, many of us found this out the hard way – at 3am during a disaster recovery session. Books Online would tell us to use setup.exe to rebuild the system databases:
start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>
OK, so what about SQL Server 2008? Well, you would have thought Books Online would save the day again, but no, the information there is not correct. It has changed. I had to search a Microsoft Customer Service blog to find the answer:
setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=instance_name /SQLSYSADMINACCOUNTS= accounts [/SAPWD=password]
Again, this needs to be practiced, because you know it’s going to happen…
I remember when Microsoft took over SQL Server 15 years ago. At that time, their objective was to make it a self tuning database that was so easy to use, you would not need those expensive DBAs to administer it. Mission accomplished? I don’t think so…
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.