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

LifeAsBob

  Thursday, July 19, 2018

 


Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

 

ATVS picking Corn.JPG

  bob Links
active server pages,...
Never argue with an idiot-they will drag you down to their level and beat you with their experience. - Dilbert Wisdom
Wider View Insert

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

Insert Category

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.