SQL SERVER – Restore Master Database – An Easy Solution

Today we will go over two step easy method to restore ‘master’ database. It is really unusal to have need of restoring the master database. In very rare situation this need should arises. It is important to have full backup of master database, without full backup file of master database it can not be restored.

It is necessary to start SQL Server in single user mode before master database can be restored. It is very easy to start SQL Server server in single user mode. Follow the tutorial SQL SERVER – Start SQL Server Instance in Single User Mode.

Once SQL Server instance is running in single user mode, immediately connect it using sqlcmd and run following command to restore the master database.

RESTORE DATABASE master FROM DISK = 'C:\BackupLocation\master.back' WITH REPLACE;
GO

I have tested it couple of times and it has worked fine for me. If you encounter any error please leave a comment and I will do my best to solve it.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

44 thoughts on “SQL SERVER – Restore Master Database – An Easy Solution

  1. Pingback: SQL SERVER - Reasons to Backup Master Database - Why Should Master Database Backedup Journey to SQL Authority with Pinal Dave

  2. I tried but didn’t work. It comes up with error as

    ” The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.”

    Please send me the solution.

  3. Something related to DR only.

    We were trying to re-build a SQL Instance from SYS+Usr db backups taken from the original machine SQLsvr 8.0.
    Here are the directions….

    1. Made a NEW SQL instance Install & patched to the same level of Original Machine.
    2. Restored the master database
    (the SQL Instance restarts after this but fails to come on line because MODEL & MSDB cant be recovered.)
    3. Using -T3608 we can set the Instance to recover no databases other than the master.
    4. SQL Instance is now online but one cannot restore any of the database backups because TEMP db is not there.

    Someone has any work around for this?

  4. I can get into single user mode but when I try to run sqlcmd it won’t run says I’m in single user mode and only one admin can connect.

  5. I have tried to restore my databases onto my pc after i have formatted it and reinstall a clean copy of SQL server 2008. I am able to restore my master db but i can’t login using Windows Authentication after i have restored the master db. I received the error message “Error: 18456, Severity: 14, State: 11″. I have used the same domain name and user name for my new windows.

    I can still login using ‘sa’ and all the other SQL Authentication logins. When i drop and recreate the Windows Authentication login, i am able to login using Windows Authentication again. but i lost all my settings for that login.

    currently i am using windows 7 and sql server 2008.

    • I have solved my previous Windows Authentication login failure issue. I am able to login using Windows Authentication after changing the SID of the Windows Authentication login to the original SID of the Windows Authentication login before i restore my master db.

  6. I am also getting the same thing at Dave Schutz – I can get into single user mode but when I try to run sqlcmd it won’t run says I’m in single user mode and only one admin can connect.

    I don’t understand why, if I am the only one trying to connect. Please advise ASAP.

    • Nevermind. I figured it out. I still had the FullText Search, Analysis Service, Reporting Service and Server Browser running. I stopped those services and was able to run sqlcmd.

      • reefshark,

        You’re correct. SQL Server won’t let you connect until you stop other services(i.e SSAS, SSRS etc). I was having a same problem when restoring master database but after following your suggestions, I’m able to restore master database. Thanks and thanks to Pinal Dave as well for such a great tip.

        Regards,
        Zehng

  7. After you run the command to restore the master database, what is supposed to happen? When I ran the command, a message flashed in the sqlcmd window for a fraction of a second (too fast for me to see what it was), and the window closed automatically. If I try to open sqlcmd again, the message flashes and the window closes again. What does this mean?

    Should I just go ahead and stop the SQL Service, and start it up again without the single-user mode?

    Thanks for your help.

  8. I am upgrading from SQL 2005 to SQL 2008. I backed up all the databases before beginning the process, but now all of the principle logins are orphaned, and I cannot get the masterdb to restore over master. Is there anything that I can do, or am I destined to recreate all of the individual user accounts and database level permissions? Thank you for your assistance.

  9. Hi.
    I am getting following error while restoring backup file.

    cannot restore a database other than master when the server is in single user mode.

  10. Up to my concern

    Take the full backup of master and system databases
    -copy the sql server 2008 setup files to the local hard drive
    -go to cmd and navigate to the setup.exe file from the hard drive
    - then type the below command
    start /wait setup.exe /qn instancename=”mssqlserver” reinstall=sql_engine rebuilddatabase=1 sapwd=”xxxx”

    {xxxx=sa password
    instance name=”mssqlserver” for default instance
    instance name=”mssql$test”for named instance}

    - after the command the rebuild process will take 5 mins
    -once the rebuild is completed connect the sqlserver using ssms.Restore the userdefind database fron the data and log files.
    -To restore the master database we hav to run the sql server in single user mode by the command
    net start mssqlserver /c /m
    then restore the database with replace option.

  11. Hi, I’m using SQL Server 2005 Standard Edition & wanna to restore master db.I have switched mode to Single user. Now when i connect to sql instance using sqlcmd using following command ,
    sqlcmd -S -d master -U sa -P ***
    an error message appears “msg:18456, login failed for user sa’ although login credentials i’m using are correct. What may be problem if anyone have idea

  12. Hi, the prev. mentioned error has been fixes, at the time m facing following error while trying to connect my sql instance of SQL Server 2005 Standard edition using sqlcmd,
    “D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlcmd -S Mjunaid -U s
    a -P lumensoft2003″
    Msg 18461, Level 14, State 1, Server MJUNAID, Line 1
    Login failed for user ‘sa’. Reason: Server is in single user mode. Only one admi
    nistrator can connect at this time. Need help in this regards

  13. Hi,
    Actually after restoring database when i tried to start up server it giving error so , what will be solution of it? Please reply me as soon as possible

    Thanks,
    Jolly

  14. Hi,
    Actually after restoring database when i tried to start up server it giving error so , what will be solution of it? Please reply me as soon as possible

    Thanks,
    Jolly

  15. hello, I have accidentally deleted the partition that holds all the system databases (master, model, msdb, etc…) I was able to get nearly everything back using a Tivoli file level restore but the databases could not be backed up so they could not be restored. So now I have an instance that cannot be started. I have the system databases backed up using sql backup but I cannot restore them because sql server will not start. Catch 22 please advise.

  16. I was trying to transfer the data file of my master database, using the following command,

    alter database master
    modify file
    (
    name = ‘master’,
    filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL1\MSSQL\DATA\Master Database Files\master.mdf’
    )

    The above command worked fine,but after that when I restarted the SQL instence ,
    the Master DB got corrupted and after that I tried a lot to rebuild the MASTER database,
    But not able to rebuild the MASTER database properly…getting some error.Can anyone give me the exact spets,syntax for rebuilding MASTER DB?..
    I dnt have any backup of Master DB,so rebuilding is the only one solutions.

    • If you can find the location to your master.mdf and master.ldf, you can try to attach it back in the Configuration Manager>Advanced tab. At the -d and -l parameters, provide the location to your data and log files respectively.

  17. Hi,

    I restored master database from our old server which has many database with single user mode. Now I am not able to startup the sql(2008 R2) server. I have many database bases in the old server where in new I have just created only one user database.
    Error : The request failed or service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

    Thx,

    IS

  18. once my master db is corrupted i tried to connect sql server but i was unable connect sql server.How to start sql server in single user mode and then how i ve to proceed

    • If you have any FULL backup of the master DB ,restore it from command prompt (But make sure you are not restoring any other server’s Msater DB backup like the previous person IS),

      If not available try to rebuild the master Db from the command prompt using the following command(in this case you will have to re create all the logins again & the previously existing logins will be lost)…

      setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= /SQLSYSADMINACCOUNTS= /SAPWD=password

  19. I have a query now, we are using an application running with SQL server 2008. Here the problem is, I am trying to make a test server by taking the backup of user table from the production server. I am not able to connect. I found that all the application user access were created under the security of the user database. SO is it required to restore the master database in into our test server? or is there any other way? In the production server, we have many application/user databases where in our test server we have only one database for that particular application. Please advise.

  20. Could I restore the master database on another server and another version of SQL, for example, a standard version to an enterprise version, you need to do to do

  21. Our server crashed last week, but a tech was able to recover the entire database data directory (including the master) and copied it to a new WIndows server. If I re-install SQL Server on the new server is there an easy way to “attach” or recover from the old data files? Tks.

  22. Hello Dave,

    I don’t understand something if the master.mdf is corrupted how you can run sqlcmd command.

    Cheers,

    Eric

  23. hai im raja
    iam tring to ssms throuh commnad line utility but it was not working i
    get this Msg 18461, Level 14, State 1, Line 65536
    Login failed for user ‘user123-PC\RAJA’. Reason: Server is in single user mode. Only one administrator can connect at this time.

  24. hi,

    i am not able to login to sql server 2008 r2 after restoriong the master database,the restore also not done properly and now i changed the server to multi user mode and now also i am unable to connect to sql server in any way mean through any login.can u please resolve my issue….

    thanks in advance

  25. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  26. great reference, but you must already have a working copy of master to get the server to start in single user mode. this is the not so minor detail that always seems to get left out. I had to take a copy of master from my development environment and place it on the server so that sql would start enough to allow me to perform a restore!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s