Crib Sheet - SQL Server 2005 - System Database(s) Restore

Restore Master database

1 - Stop and disable instance services (Server, agent, FT search)

2 - Start instance in single-user mode from DOS prompt

NOTE - you should be in the BINN path for commands to work

3 - Type sqlservr.exe -s<instanceName> -c -f -m   (required for Master database)

NOTE - replace <instanceName> with the actual instance name, unless it's a

             default instance; in that case don't use -s<instanceName> at all

4 - Log into instance from second DOS promt

5 - Type sqlcmd -S<serverName>\<instanceName>

NOTE - replace <serverName> with actual server name and<instanceName>

             with the actual instance name, unless it's a default instance; in that

             case just use the server name

6 - From the 1> prompt restore the system database

7 - Type restore database <dbName> from disk=<path> with recovery, replace

NOTE - replace <dbName> with the database to be restored, <path> with

             the path to and backup filename in quotes

When done the instance stops automatically

NOTE: The Resource database depends on the location of the master database.

            If you move the master database to a new location, you must also move the Resource

            database to the same location as the master data file.  May require –T3608 trace flag

8 - If you need to move the resource database, type the following commands

9 - ALTER DATABASE mssqlsystemresource

     MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');

     GO

     ALTER DATABASE mssqlsystemresource

     MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');

     GO

NOTE - replace 'new_path_of_master' with the new location of the master database

10 - Repeat for the Model and MSDB databases, except you now only type

       sqlservr.exe -s<instanceName> -c

Actual source unknown, though I received this from SQLCricket.