Post reply

Start SQL Server 2005 in Single User Mode FROM SSMS

  • July 11, 2007 at 8:28 am

    #117374

    Does anyone know how to start a SQL Server 2005 instance in single user mode from Management Studio? I had to do a complete restore last night, and was somehow lucky enough to get the master restored without switching to single user mode, but like I said, I think I got lucky. I understand that the command line commands are something like for a default instance:

    cd \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

    sqlservr.exe -m

    However, when I ran that command I couldn't connect to the server from SSMS. Did I run a bad command or is there a better way to start SQL Server in Single User Mode so I can still use SSMS to restore the master?

    Thanks for your time,

    jim

  • July 11, 2007 at 8:32 am

    #718648

    Jim

    you can't start SQL Server in single-user mode from SSMS. You have to use the command line option. After that you should be able to connect from SSMS. Which error message do you get?

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • July 11, 2007 at 8:45 am

    #718658

    I got a can't connect error message, I'm sorry as I don't remember the exact wording, but it was definitely a can't connect to the SQL Server instance type of error.

    When you run command to start in single user mode from the command line, do you wait for the prompt to come back?

    I'd run the command and wait for about 5 minutes and see a bunch of messages scroll down the page saying no user action is required, but I never was able to get the command prompt back.(It's like the command wouldn't finish running or something.) I think that I might have not been waiting long enough or something. I did have the SQL Server Agent Service Stopped.

    This was my first total restore from catastrophic failure, I've restored many user databases, but never needed to restore a master.

    Thanks,

    jim

  • July 11, 2007 at 9:54 am

    #718687

    hi Jim,you cannot start from SSMS in single user mode

           You can start sql 2005 in single user mode by performing the below steps,

    1.go to cmd and type net start mssqlserver /c /m

    2.now you can connect to SSMS and right click the dbs and perform restoration.......inorder to restore the master you need to login with single user mode i dont understand how you managed to restore master without being in single user mode 

    b4 performing step 1 stop the sql server.......and also after performing step 2 i.e restoring all the dbs go to the cmd which you opened and press ctrl + c to exit from single user mode....do not close the window as such....

    you can also start using sqlservr.exe -m but its always preferable start with / option instead.......

    [font="Verdana"]- Deepak[/font]

  • July 11, 2007 at 9:58 am

    #718690

    moreover you can even restore master from cmd by using sqlcmd utility...but for that too you shud start sql as i mentioned......after starting sql in single user mode, just type sqlcmd in the same command prompt and then type in your query...use GO command to separate the batches...this option too is possible

    another option is go to sql server configuration manager and go to sql 2005 services and select sqlserver right click go to properties and then to advanced tab and startup parrameters.....it will be like this,

    -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-m

    i have added ;-m parameter @the last to start sql in single user mode ,once it is done restart sql server and connect to SSMS and restore as usual

    [font="Verdana"]- Deepak[/font]

  • July 11, 2007 at 10:36 am

    #718710

    Thanks Deepak,

    I really appreciate your help!

  • July 25, 2007 at 10:29 pm

    #722473

    hi,

     

    In Object Explorer, connect to an instance of the SQL Server 2005 Database Engine, and then expand that instance.

    1. Right-click the database to change, and then click Properties.

    2. In the Database Properties dialog box, click the Options page.

    3. From the Restrict Access option, select Single.

    4. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

    You can also set the database to Multiple or Restricted access by using this procedure. For more information about the Restrict Access options.<script type=text/Javascript>var ExpCollDivStr = ExpCollDivStr;ExpCollDivStr = ExpCollDivStr + "ctl00_LibFrame_ctl08d527b88,";var ExpCollImgStr = ExpCollImgStr; ExpCollImgStr = ExpCollImgStr + "ctl00_LibFrame_ctl08img,";</script>

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply