Sessions: 207 The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob

  Wednesday, May 29, 2024
Wider View Login

Cluster
DOS
IBM - AS400
MOM
Performance Counters
Service Broker
SQL Server
     Temporary Database
     Views
     Backup
     SQL Mail
     DTS - Data Transformation Services
     Memory
     Security
     Excel
     Jobs SQL Agent
     MSDE
     Install
     DBCC
     User Management
     System Databases
          Crib Sheet - SQL Ser...
          Moving the MSDB Data...
          Moving the Model Dat...
          Moving the master da...
     BCV
     Deadlocks
     Temp Tables
Windows OS




Will be added as a sub-category of, System Databases
Crib Sheet - SQL Server 2005 - System Database(s) Restore

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.