Monday, April 27, 2009

Restoring master and msdb Database

Restoring master database requires sql server running in single-user mode. We can do the following:
1. Start a cmd window
2. Stop SQL Server
net stop mssqlserver
3. Start SQL Server in single user mode
sqlservr -m
4. Start another cmd window
5. Restore the master database
osql -E -Q"restore database master from disk = 'D:\SQL2000\MSSQL\BACKUP\master-2007-03-12.bak'

NOTE: after executing this command you should see the following output:
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

In step 5, we can also restore the database in Query Analyzer by running: restore database master from disk = 'D:\SQL2000\MSSQL\BACKUP\master-2007-03-12.bak'

To restore msdb database, we should run sql server in multi-user mode. If there is an error indicating exclusive access, then try to stop sqlserveragent.

No comments: