We need to rebuild the system databases if the master database is corrupted or damaged. Let us discuss in detail how to rebuild system databases in SQL server 2005.
Step 1: Take a full backup of all the System and User databases prior to rebuilding the system databases as we need to restore it later to avoid data loss.
Step 2: Copy the SQL 2005 setup files from the CD to the hard disk. In the command prompt, navigate to the folder which contains the setup.exe file. In my case it is available in D:SetupsSQL 2005Servers folder. The SQL Server we are going to rebuild is currently running. Now type the below command,
start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=XXXX
where XXXX is the name of the password.
INSTANCENAME=MSSQLSERVER for default instance of SQL 2005 and
INSTANCENAME=MSSQL$INSTANCENAME for named instance of SQL 2005.
If you have a named instance named as DeepakTest then type as below,
INSTANCENAME=MSSQL$TEST in the above command
Refer the below screenshot for the same.
Step 3: After executing the command in step 2 the rebuild process will start and will complete within 5 minutes. You can verify whether the databases are rebuild by navigating to folder containing the data and log files for the system databases. If you arrange them using modified date it will clearly show the time when it was last modified and it is the time when we executed the command in Step 2.
Step 4: Once the rebuild is completed, connect to the SQL server using SSMS. In the object explorer only the system databases will be available.
If any user db were present prior to rebuild it will be lost and we need to perform as below to retrieve it.
1. Restore from the backup taken in Step 1 (or)
2. We can attach from the data and log files of the user db as they will not be cleaned up during rebuild process.
NOTE : No Need to detach all the user databases before rebuild as the ldf and mdf files will be present in the same path as usual and will not be overwritten.
Now we need to restore the system databases from the backup which we took in Step 1.
Master database can be restored only in Single user mode (refer Step 5) and other dbs can be restored normally.
NOTE : The ldf and mdf files of the system databases will be overwritten and hence we cannot perform detach/ attach operation.
Step 5: In order to restore master database perform the below steps,
Stop SQL server and start it using the below command from the command prompt
NET START MSSQLSERVER /c /m which will start SQL in single user mode
Note: For default instance its MSSQLSERVER, for named instance its MSSQL$instancename
Type as shown in the below screenshot. Once the restore is completed SQL server will shut down and hence we need to start it normally and access the databases.