Restoring the Master Database

SQL Server 2000

Stop the SQL Server instance. And start it in single user mode. Open a command prompt window. Change into you SQL Server Binn directory for the instance you are repairing (the default is c:\Program Files\Microsoft SQL Server\MSSQL\Binn) and run:

sqlservr.exe -c -m
The -c option tells SQL Server to run independantly, and not as a service. The -m option tells SQL Server to run in single-user mode.

Your sql server instance will then start up and you can connect to it with Query Analzyer.

If you get the error message:

Server: Msg 18461, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failer for user 'x'. Reason: Server is in single user mode. Only one administrator can connect at this time.
then you need to make sure that no other administrators are accessing the server. If you have Enterprise Manager or the SQL Server Service Manager open, close them, and try again. Make sure that any other services (like SQL Agent) that access SQL Server are also stopped.

Once you have successfully connected to your SQL Server instance in Query Analyzer, restoring the master database simply requires running the RESTORE DATABASE command:

RESTORE DATABASE master FROM DISK='N:\Backups\master.bak'
SQL Server should return:
The master database has been successfully restored. Shutting down SQL Server. SQL Server is terminating this process.

Once your master database is restored, SQL Server will reboot and attempt recovery on the other databases listed in your master database. Check the error log again to see what databases have SQL Server is unable to recover.

2005-12-28 16:16:00.66 spid11    Starting up database 'HHH'.
2005-12-28 16:16:27.85 spid11    Error: 9003, Severity: 20, State: 1
2005-12-28 16:16:27.85 spid11    The LSN (72984:107911:2) passed to log scan in database 'HHH' is invalid..
2005-12-28 16:16:28.07 spid11    Error: 3414, Severity: 21, State: 1
2005-12-28 16:16:28.07 spid11    Database 'HHH' (database ID 7) could not recover. Contact Technical Support..

During the recovery of this server I found four databases were unrecoverable. Again, restoring these databases from their latest backup resolved the problem.

To ensure that all the databases were infact ok, I ran DBCC CHECKDB. A quick way to run it on all databases is with this script:

EXEC sp_MSforeachdb 'PRINT REPLICATE(CHAR(13),3) + REPLICATE(''='',80) + CHAR(13) + ''Checking: ?''
		     DBCC CHECKDB (''?'') WITH NO_INFOMSGS'


Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Restoring the Master Database | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.