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
Without any Master DB backup, How Can I restore Master database.
Regards,
Subir
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.
I think I found answer to my problem https://www.microsoft.com/en-us/download/details.aspx?id=51958 which explains how to rebuild your master model, msdb database so that you can restore them.
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.
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
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
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.
I will buy you beer or whatever beverage you want for your great tips, if we ever meet :)
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
Why do you want to do this?
Perhaps they have reached the limit of the SQL Express max size?
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.
Hello Dave,
I don’t understand something if the master.mdf is corrupted how you can run sqlcmd command.
Cheers,
Eric
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.
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
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!
The both mdf and ldf files should be location as the primary server path. Otherwise this script will not work.
SQL CMD NOT WORKING
ii tried but facing this type of error. plzz help me….
Shared Memory Provider: No process is on the other end of the pipe.
Communication link failure
Hi Pinal,
There is a typo in Restore Syntax. Extension should be .bak
RESTORE DATABASE master FROM DISK = ‘C:\BackupLocation\master.back’ WITH REPLACE;
GO
Vaibhav Shukla
Thanks for bringing to my attention. I have fixed the same.
sir i have master database of 2008…. when am going to restore that master database to sql server 2008R2… itz not restoring unable to Restore… when am gone to restore its showing Error RESTORE HEADERONALY IS TERMINATING ABNORMALLY (MS SQL SERVER ERROR:3241)
You cannot restore a SQL 2008 system database onto SQL 2008R2 or any different version for that matter (2005, 2012, 2014, 2016, etc). The system databases need to go back onto the same version (even SP/CU I believe). If you need to restore your master DB, I would recommend installing a fresh SQL2008 install, restore master, then upgrade that instance to 2008R2.
That being said, restoring master is a risky process to begin with and I know I’ve personally never needed to restore master (or any system db) before. The one time my system databases got corrupted, I did a fresh install of SQL and attached the user DB’s and recreated all the logins manually.
The other option you have is to restore master onto a fresh 2008 instance, restore master then script out all of the objects you need (users and permissions)
Nice article. It worked for me. Thank you Pinal