May 25, 2008

Restore master database - Sql Server 2005

If you want to restore your master database then you need to follow those steps-
1. Disable all services of Sql server. One way to do this is- Open 'Sql Server Configuration Manager' then disable all services running under 'Sql Server 2005 services'.
2. Now run Sql server instance in single user mode by running
NET START "Sql Server (InstanceName)" /m
3. Now open the sqlcmd or Management studio and launch New Query window. (If you connect to instance and then try to connect to new query, it wont allow as you already connected to Instance. So one way is to disconnect to instance from Disconnect button in Management studio. Then click on New Query, it will ask for Username and Pwd)
4. Now restore your master database using
use master
Restore database master from disk = '...'
5. Now all changes in master after the backup you restore will be lost. So if you have created any users/logins you need to create then. If you have any databases created after that, you will not be able to find them under System databases. So you can Attach them from management studio from the Data file path of sql server.

No comments: