How to Rebuild System Databases in SQL Server 2005

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.

For example,

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.

rebuilesysdb1

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.

rebuilesysdb2


Posted

in

by

Comments

2 responses to “How to Rebuild System Databases in SQL Server 2005”

  1. Sunil avatar
    Sunil

    Hi Deepak,
    This blog is very very helpful to may dba’s and all the scenarios are something different.

    I’ve a doubt in restoring model database in Sql server 2008 R2. I lost my model database .mdf and .ldf from the disk, but we have a last night backup. When i’m tryiing to restore the database, it’s giving the error:
    HResult 0x6D, Level 16, State 1
    Shared Memory Provider: The pipe has been ended.
    Sqlcmd Error: MIcrosoft SQL Server Native Client 10.0: Communication link failure.

    I used the flags /T3608 and /m while starting the service and
    sqlcmd -E
    >restore database model from disk=” with replace
    >go

    1. VidhyaSagar avatar
      VidhyaSagar

      @Sunil — Is shared memory protocol enabled in SQL Server configuration manager? If not enable it and then restart sql server services.

Leave a Reply

Your email address will not be published. Required fields are marked *