Thursday, November 25, 2010

How to rebuild system databases in SQL Server 2005

When system databases are rebuilt, all database objects and data in master, model and msdb system databases are removed. Rebuilding the master database installs all system databases to their initial location.

To rebuild the system databases you need to run the setup command from the Command prompt and follow the following procedure:

1. Click Start, click Run, type cmd, and then click OK.
2. Run the following command to rebuild the system databases:

start /wait \setup.exe /qn INSTANCENAME=Instance_Name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=StrongPassword

For example:

start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=p@ssw0rd

Notes about this procedure:
 

bullet 
Put the media (CD or DVD) on the same location from which you originally installed the instance of SQL Server 2005.
bullet 
The /qn switch makes all Setup messages, including error messages, to be written to Setup log files. The main log is located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt
bullet 
The /qb switch can be used instead of the /qn switch. /qb allows the display basic setup dialog boxes and all error messages.
bullet 
SAPWD is needed to specify a new password for the System Administrator account. Microsoft recommends de use of a strong password.
bullet 
The parameter INSTANCENAME is used to specify the instance name. Use MSSQLSERVER for the deafult instance.


The above procedure is often used to rebuild the master database for a corrupted installation of SQL Server.

References:

http://msdn2.microsoft.com/en-us/library/ms144259.aspx