Search This Blog

Monday, August 29, 2011

SQL DBA - How to change the SQL Server in Single user mode & How to restore the Master DB

1. MASTER database is a different beast from all the other databases.
2. The MASTER database stores the logins and the lists of all of your databases.
3. Without the MASTER database, you can't start SQL Server.
4. It must be done in single user mode.

Changing into single user mode.
1. In SQL Server Configuration Manager, click SQL Server Services.
2. In the right pane, right-click SQL Server (), and then click Properties.
3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;).
4. For example, to start in single-user mode, insert -m; in front of the existing startup options, and then restart the database. (When you start SQL Server in single-user mode, first stop SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.)
Note :
After you are finished using single-user mode, you must remove the -m; from the Startup Parameters box before you can restart the server instance in the normal multi-user mode.
5. Click OK.
6. Restart the Database Engine.
Restoring Master DB.
C:\> sqlcmd
1> restore database master from DISK = 'C:\temp\master-20070822-02.bak';
2> go

No comments: