none
How to restore Master DB in SQL 2005???? RRS feed

  • Question

  • I've tried starting SQL from the command line in single user mode (-m). When I try to open Management Studio I receive an error that it can't connect. Can you only restore master from a command line in SQL 2005? Or can I do it from a GUI?

    Thanks,
    Mark
    Thursday, August 3, 2006 1:31 PM

Answers

  • I would use the command

    NET START "MSSQLSERVER" /m

    Connect with either SQLCMD or management studio, and use the TSQL command:

    USE master
    GO
    RESTORE DATABASE master
       FROM TAPE = '\\.\Tape0'
    GO
    Obviously substituting your backup device/file as appropriate.  See Books Online for specifics. 
    Thursday, August 3, 2006 6:54 PM
    Moderator

All replies

  • OK, I now tried starting the SQL Server Service form the 'Services' GUI in Windows, using the parameters -c and -m. Now I am able to open Management Studio. When I right-click on the master database and choose 'Tasks-Restore-Database' master is not in the list of databases to restore.
    I tried typing it in manually, but the restore failed.
    Thursday, August 3, 2006 2:04 PM
  • You need to first start SQL Server in single user mode (or put it in single user mode), and then restore your database. So using -m should work if you can get in via Management Studio (assuming no one else gets in before you).

     

    Have you also tried using T-SQL's RESTORE DATABASE command?

    Thursday, August 3, 2006 3:34 PM
  • Thanks for the reply Allan.

    How do suggest I start SQL in single user mode.

    I tried running the sqlsrvr.exe with the /m switch from the command line, but then Management Studio wouldn't connect.

    I then tried starting the sqlserver service from the windows services gui, using the /m parameter, but  then in Management Studio "Master" wasn't in the list of databases that I could choose from when I selected the 'restore' task. I tried typing 'master' in the selection box and then ran the restore, but it failed. 

     

    Any ideas?

    Thursday, August 3, 2006 5:18 PM
  • I would use the command

    NET START "MSSQLSERVER" /m

    Connect with either SQLCMD or management studio, and use the TSQL command:

    USE master
    GO
    RESTORE DATABASE master
       FROM TAPE = '\\.\Tape0'
    GO
    Obviously substituting your backup device/file as appropriate.  See Books Online for specifics. 
    Thursday, August 3, 2006 6:54 PM
    Moderator
  • Unfortunately the NET START "MSSQLSERVER" /m command does not work for me.

    However, I can add "-m" to the Startup parameters in the SQL Server service from the Services GUI and it starts in single-user mode.  I was having trouble connecting to a query window in Management Studio, when it hit me that there were still other SQL Services running.  I think that one of the other services was getting to the one connection that was available before I could open a query.

    So I stopped ALL SQL related services, started in "-m" single user mode, and was able to open a query and restore the master database.

    It's always the little things.

    Wednesday, August 16, 2006 1:11 PM
  • I stopped all other sql services and able to open query tool in management studio in single user mode. Thanks for pointing this out.

    Friday, October 12, 2007 4:28 PM
  • Refer the below link, described in steps

    Rebuilding sysdb's

    Thursday, October 18, 2007 10:36 AM
    Moderator
  • Hey, I found a simple script that restores the master database on the following site: http://www.sqlserveroptimizer.com/2011/07/how-to-restore-master-database-in-sql-server-20052008-r2/

    RESTORE DATABASE master FROM DISK = 'e:\MyFolder\master.bak ' WITH REPLACE
     GO

    Saturday, December 10, 2011 8:20 PM