Skip Links

Rebuild the System Databases – Practice makes perfect!

By Brian Egler on Thu, 03/05/09 - 10:20pm.

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>

2005 Details: http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx

 

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]

2008 Details: http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

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…

Cheers

Brian

 

Recent Posts:

Skill-based Certification for the future?

Browser Support in Reporting Services?

Want to know what’s going on in your cube?

COBOL Support in SQL Server 2008???