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)
pinaldave
[...] writing blog at SQLAuthority.com is follow up question. Just a day before I wrote article about SQL SERVER – Restore Master Database – An Easy Solution, right following it, I received email from user requesting reason for importance of backing up [...]
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.
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.
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.