Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Restoring the SQL Server Master Database Even Without a Backup


By:   |   Read Comments (15)   |   Related Tips: More > System Databases

Problem

While researching this article I was surprised by the number of DBAs who back up their user databases, but not their system databases. They either do not fully understand the importance of these databases or have been lulled into complacency by years of smooth sailing. Whatever your reason may be, I'm going to show you how to save yourself (mostly) should you ever find yourself with no viable master database and no good backup.

Solution

There are several reasons you may need to recover your master database. You may have removed something critical and you want it back- a login, linked server or some other system object. This may be the easiest scenario because you still have a master database to start from. Your master database may have been damaged due to hardware or software failure and rendered unusable. You may be restoring to a brand new server or creating a clone of your instance.

I'll cover three scenarios in this tip.

  • First, you have a master database that is viable and you have a backup, all you want to do is a little "time travel" to get back to a known state.
  • Second, your master database is gone or unusable, but you have a backup from which to restore - thank your lucky stars.
  • Last, your master database is kaput and you have no backup to use for recovery. Don't get out your resume just yet, we can get your instance back up and running, but there will be a lot of work to do after that to return to some semblance of what it once was.

Warning! If your master database is all that's broken STOP NOW and make copies of your msdb and model database files! Some of the instructions below will overwrite them destructively. Let's not add insult to injury by whacking stuff that is still good.

Scenario 1: Restoring an existing SQL Server master database from backup

Restoring master is tricky because it contains the information about all the other databases in the instance and is required for startup. In the case where you have a "good" master database to start from you have to start in single-user mode using the -m flag. In SQL Server Configuration Manager, right-click on the SQL Server service, select properties, then the startup parameters tab. Enter -m in the top box and click add, then apply, then restart SQL Server.

Single User Setup Screenshot

Now, with SQL Server in single user mode, using the command line (SQL Server Management studio won't run without in single user mode!) you can recover your master from backup. Find sqlcmd.exe in your .../Tools/Binn directory and run it. You will need to use the -S flag if you have a named instance and the -U and -P flags if you're not using a trusted connection (see BOL for more information). From there you can restore from your backup just like any user database.

Restore Master Database Screenshot

That's it, now remove the -m from the SQL Server service startup options and restart the SQL server service in multi-user mode! Detailed master database restore instructions are on MSDN HERE and there is a very good series of tips on MSSQLTips.com HERE to help you prepare for a master database rebuild.

Scenario 2: Rebuilding then restoring the SQL Server master database from backup

If you don't have a viable master database you need to create one first, if your master database is there and you just want to recover from backup use scenario one. There are two ways you can rebuild your master database. For 2005, 2008 and 2008R2 you must use setup to recreate ALL of your system databases at once, for 2008R2 and later you can use the template option to overwrite just the master database.

Using Setup to Recreate System Databases

From the bootstrap directory- "C:\Program Files\Microsoft SQL Server\<nnn>\Setup Bootstrap\<release>" (replace <nnn> with the right version and <release> with the right release) run the following command substituting your instance name, a windows account to have administrative rights and a secure password for SA where indicated. This is where you want to make sure you have copies of your good model and msdb databases saved off somewhere safe!:

.\setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance_name> /SQLSYSADMINACCOUNTS=<admin_account> /SAPWD=<sa_password>
    

For Example:

Rebuild database screenshot

Now that you have a master database to work with you can recover it from a backup if you have one. At this time you should also shut down the services and replace the blank msdb and model database files with the copies you saved off previously. Jump to scenario three to find out how to recover at least some of your master database's data if you're not recovering from a backup.

Using the Template Master Database

For 2008 R2 and 2012 the system database recovery function works differently. Rather than using T-SQL scripts there are template databases created at install time, which are copied over the database and transaction log files of your system databases by the setup.exe /REBUILDDATABASE option. If your msdb and model databases are okay then it may be simpler to manually copy the template files of the master database to where they belong rather than rebuild all three system databases and restore the msdb and model from backup. The template files are found in the "C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\Templates" directory depending on your instance name and version.

If you have a good backup of your master database you can now restore it using the instructions from scenario one. If you don't have a backup then keep reading to see how you may be able to get back some of the data you're missing. Start your instance and follow along as we try to get stuff put back together.

Scenario 3: Reconstructing un-backed up data from the SQL Server Master Database

First, if you skipped the first sections of this tip, follow the instructions above to re-create your master database making sure to keep good copies of your other system databases in case they get overwritten by the rebuild.

At this point you should have an instance up and running, if you have master database backups and have restored then congratulations, you may breathe a sigh of relief and go on to the next tip. If you had no backups then we still have some work to do.

Reconnecting the databases

Connect to your server using SSMS and the Admin account you used when it was installed or that you used on the rebuild step above. The first thing you will notice is that when you expand the databases tree it is empty. This is because master contains all the database location information and its now gone. But the databases are still out there, we just need to tell SQL Server where they are. If you have backups of these databases the easiest solution is to use them now, if you don't then this will take some legwork as you will need to record the location of all the database and transaction log files for each database and attach them manually using the either the following T-SQL code with the right database name and file names replaced or SQL Server Management Studio's attach database command from the database menu:

USE [master]
GO
CREATE DATABASE [UserDatabaseName] ON
  (FILENAME = N'C:\sqldata\data.mdf'),
  (FILENAME = N'D:\sqltlog\tlog.ldf')
FOR ATTACH
GO
    

Right click the databases tab and select "Attach " then click "Add" on the Attach Database dialog. By selecting the mdf file for each database it will find the .ndf and .ldf files (if they have not been moved).

Attach database screenshot

Note that you may need to change the ownership of the databases, they will be owned by the account that re-attached them. You will need to recreate the logins first!

alter authorization on database::[database_name] to  "owner_name"
    

Restoring other system objects

There are several other object types that exist in the master database that will be lost when you rebuild or restore from template and don't have a backup; logins, endpoints, and linked databases to name a few. This is where the rest of the restore gets either really tedious or impossible, but look on the bright side - you'll be ridding yourself of all that baggage that has been accumulating for years. If similar objects exist in other instances (your QA or development environments, for example) you can use SSMS to make create scripts to edit and deploy on the restored server. Even if the objects are not an exact match, the scripts will be close enough to save you a lot of needless typing.

Next Steps
  • First and foremost, without further delay, create and implement a backup plan for your system databases! It's as simple as setting up a maintenance plan. Once you've got that setup, take a look at your backup plans in their entirety (you do have them, right?) and review them with this experience fresh in your mind.
    • Are your system databases backed up frequently enough to meet your recovery point objectives?
    • Are you user database backups actually running and will they met your RPO?
    • Where are the holes, if any, that you want to fill and what will it take to fill them?
    • If you're not backing up due to budget constraints you need to make a business case for a tape drive or external drive of some sort, they are far cheaper than lost business due to a prolonged outage or worse- lost data.
  • Backup and recovery are a core skill of any DBA (on any platform) make sure you have backups and make sure you regularly test not only your backup media, but your processes and procedures as well. Crunch time with your CIO looking over your shoulder is not the time to verify that you recovery procedure it 100% up-to-date!


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author John Grover John Grover is a 25-year IT veteran with experience in SQL Server, Oracle, clustering and virtualization.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, January 12, 2018 - 5:54:51 AM - Bizzy Back To Top

Thanks you very much! helpful

 


Saturday, October 07, 2017 - 11:27:31 AM - satish p Back To Top

 

 thank you it is helpful to us.


Monday, April 03, 2017 - 9:44:18 AM - John Grover Back To Top

Shashikant-

No you cannot use just an LDF to re-create a database. The LDF is a transaction log file and only contains information about changes in the database for a period of time, not the data itself. I would first hunt for a backed up version of the master.mdf and failing that you'll need to recreate from scratch following one of the procedures above.


Monday, April 03, 2017 - 4:01:12 AM - Shashikant Patil Back To Top

 Hi,

My .MDF is deleted accidently, But we have .LDF file.

So, Is it possible to recover data from .LDF file???

Please help.

Shashikant


Friday, January 30, 2015 - 10:58:03 AM - John Grover Back To Top

The procedures I've found only go back as for as SQL Server 2005, for anything older than that I'm not sure that they will work 100%. I think you'll need to use the old trial and error method to get it to work unless you can find someone who is familiar with the older versions of SQL Server. Sorry I can't be of more help.


Thursday, January 29, 2015 - 6:15:27 PM - emmanuel Back To Top

 

 

I finaly found sqlserver 7.0 and i installed it . The data (tables) that I am trying to restore have been insert into the master data base( I don’t know why).  I follow your procedure in graphic mode

 

-The restore begins

 

-An error occured : “ConnectionRead(recv()) Network Error”

-Another error message: “The connection to SQL Server ‘ SERVER’ has been broken”

 

-I try to relaunch the data base restore and I receiv this message: “Error 22528: [ SQL-DMO] This server connection has been unexpectedly broken and cannot be reconnected”

 

-I stopped and restart sql server 7.0 , i relaunch the restore

 

-The same error occured : “ConnectionRead(recv()) Network Error”

 

-I restart the computer and the same error occured.

 

Please can you give me some advises?

 

 

 


Wednesday, January 28, 2015 - 2:20:20 PM - John Back To Top

Not that I'm aware of, I did a quick Google search for it and wasn't impressed with the quality of the sites that were returned. But if it's an emergency they may be worth a shot.


Wednesday, January 28, 2015 - 9:20:47 AM - emmanuel Back To Top

Thank's for your answer. There is any chance to download sql server 7.0?


Tuesday, January 27, 2015 - 8:31:06 AM - John Back To Top

Emmanuel-

A master database always has to be restored to the same version from which it was backed up. You won't be able to restore a 7.0 master to 2005.


Monday, January 26, 2015 - 5:45:21 PM - emmanuel Back To Top

The master database(d:\master.bak) has been saved on sql server 7.0 and i am trying to restore it on sql express 2005

 

 

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>SQLCMD -S GENESIS-PC\SQLEXPR
ESS -E
1> RESTORE DATABASE [master] FROM DISK = "d:\master.bak" WITH REPLACE ;
2> go
Msg 3168, Level 16, State 1, Server GENESIS-PC\SQLEXPRESS, Line 1
The backup of the system database on the device d:\master.bak cannot be resto
red because it was created by a different version of the server (7.00.0623) than
 this server (9.00.4035).
Msg 3013, Level 16, State 1, Server GENESIS-PC\SQLEXPRESS, Line 1
RESTORE DATABASE is terminating abnormally.


Tuesday, November 11, 2014 - 1:21:44 PM - John Back To Top

Eric- did you restart the SQL Server service after changing the parameter? Also, sometimes you need to hit "apply", then "ok" to make sure your change actually takes. You can go back into configuration manager to verify your change is saved.


Monday, November 10, 2014 - 4:54:02 PM - eric Back To Top

I've added -m in my startup parameters but still system says its not a single user mode.


Thursday, July 24, 2014 - 11:28:56 AM - Daniel Fountain Back To Top

You can open management studio in single user mode - as per this link:

 

http://msdn.microsoft.com/en-us/library/ms188236(v=sql.105).aspx

 


Wednesday, July 02, 2014 - 6:17:46 PM - Dan Carvin Back To Top

About a year ago I took it upon myself to figure this out, and test it on a non-production instance. Surprisingly it was more difficult than it should be. For one thing, BOL doesn't have all the steps in one page.  Next, the process for recreating the system databases is slightly different for SQL 2005, 2008 and 2008 R2. If the blog post you are working from is for different version that the instance you are trying to fix, you may end up banging your head against the wall.  

In addition backing up the system databases along with the user DBs, I now regularly make copies of the system mdf and ldf files and store them on the c: drive (assuming the live copies are not on c:), ready to drop back into place in case of corruption or loss of the system database drive. 

Thanks for the post describing the process for 2008 R2 and 2012. 


Wednesday, July 02, 2014 - 5:51:12 PM - Perry Whittle Back To Top
The attach script you supplied will create a database with 3 files, this is the correct syntax
USE [master] 
GO 
CREATE DATABASE [UserDatabaseName] 
ON (FILENAME = N'C:\sqldata\data.mdf')
LOG ON
 (FILENAME = N'D:\sqltlog\tlog.ldf') 
FOR ATTACH 
GO 

Learn more about SQL Server tools