How to rebuild master or system databses

Hi Friends

We can rebuild system databases in SQL server 2000 using rebuildm.exe file but from SQL Server 2005 onwards we can use setup.exe file to rebuild system databases.

If the server is running in Windows Authentication Mode we can use the following command

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance
name> /SQLSYSADMINACCOUNTS=<accounts>

If the server is running in Mixed Mode Authentication then you can mention sa account details including password as follows

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance
name> /SQLSYSADMINACCOUNTS=<accounts> /SAPWD=password

Introduction

The worst has happened! An unexpected shutdown of SQL Server 2008 database server has resulted in crashing of the SQL Server. I was unable to connect to SQL Server Instance and when I checked the SQL Server error logs, I could see the following entries “Restore the database from a full backup, or repair the database”, “Cannot recover the master database”. Now what do I do? I don’t have backups of SQL Server System databases!
The questions which came to my mind were:-

How can I bring the server back online at the earliest?
Do I need to uninstall and then reinstall the SQL Server 2008?

Today I realized how important it is to backup system databases across environments.

Error Messages in SQL Server Error Logs

During redoing of a logged operation in database 'master', an error occurred at log record ID (206:424:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

1. If you have SQL Server 2008 Installation media available then insert the same into the disk drive and search for Setup.EXE file or else search for Setup.EXE file which will be available locally on your server. The default location of Setup.EXE file on your server will be C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release.

2. In the command prompt window you need to enter the below mentioned command with appropriate values for each parameter and press enter to rebuild the system databases as per the syntax which is mentioned in MSDN.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts [/SAPWD= StrongPassword][/SQLCOLLATION=CollationName]

If you want to change the server level collation then specify collation value for /SQLCOLLATION parameter. As /SQLCOLLATION is an optional parameter, if it is not specified then the current server collation is used to rebuild the system databases.

3. Once the setup has successfully completed rebuilding the system databases it will return to command prompt and will mention the SQL Server Build Number as shown in the snippet below. In case if there are any syntax related or permission issues then the specific error message will be displayed within the command prompt window. You need to fix those issues before you can complete the activity.

 

4. You can view the overall summary from C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt

 

Conclusion

In this article you have seen how to rebuild system databases in SQL Server 2008. You should rebuild system database only under conditions when you don’t have backups of system database or they are corrupted. When you rebuild system database it rebuilds MASTER, MSDB, and MODEL databases as a result you will lose all your Linked Server, Logins, SQL Server Job information.