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.bck' 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 (https://blog.sqlauthority.com)
53 Comments. Leave new
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.
Hi. check the parameters you just added and verify that you did not add a space after typing ‘-m;’.
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?
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.
Before get into SQLcmd, how do you start SQLServer?
Sounds good. We will test…
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.
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
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.
sir,wat we can do with MIB file ? and ia that we can re use it ?
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.
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.
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.
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
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
Goto the property of the database and set Restrict Users to multi_user
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
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
If master database is full pls tell me recommendations.
What about restoring master in different instance. ie. migrating to another server
How do you do for in a Cluster?
Thanks
Thank you very much Pinal Dave… your blog make restore master database very easy and simple…