Problem:
One of the biggest mistakes that most of the DBAs do is backing up their user database but not the system databases. May be, they cannot correctly predict the importance of system database or the smooth functionality of SQL Server never made them confront the need to backup this database. For any excusable reason, if backup for system database is not maintained, here is a tip shared that will help to get back the master database back even if no backup is available.
Solution:
First of all, why master database is so important that it must be backed up. This database stores all system–level information of the server. For example: The login info, the servers linked together, and other server level objects are stored in this database. This database can get into an inconsistent mode due to varied reasons that include hardware or software related issues which may restrict the server to get up and render its services.
In the later sections, I will be covering solution to restoring a damaged master database via following mediums:
#: Backup of master database is available and it has to be restored following the RTO challenge. The restoration of this DB is considered risky because it comprises of information about other databases and is a crucial component for starting up the database.
To get started, it is important that SQL Server single-user mode is activated. For that, open ‘SQL Server Configuration Manager’ and click on ‘Startup Parameters’ tab. In the text box for startup parameter, enter “-m” flag and click on ‘Add’ button. Click Apply and restart SQL Server.
To get the single user mode activated, the services has to be stopped and restarted again.
Now, for restoring the database through SQL Server Management Studio, the process is simple. Select the system database that has to be restored, right click on it, choose ‘Tasks’>> ‘Restore’>> ‘Files and Filegroups…’.
You will be presented with two different pages: “General” and “Options”. Select them and make relevant modifications in settings for restoration.
#: Rebuild Master Database and Restore it from Backup.If a workable master database is not available, it has to be first created. If a master database is already available, then the scenario and solution shared above will work. There are two methods that can be adopted for rebuilding the database.
1) Use the Setup for Re-Creating the Master Database
In the process of rebuilding the master database, it is important to know that the resultant will have three databases created:master, model and msdb. For SQL Server 2012, there is no need for setup DVD/ISO. Run the following command from the bootstrap directory (C:\Program Files\Microsoft SQL Server\<SQL Version>\Setup Bootstrap\<Release>).
Syntax Details:
The values for these parameters should be replaced accordingly. Before running the command, make sure that a healthy copy of model and msdb database is saved as safe location.
Now when the master database is in a workable state, it can be recovered from the available backup. For this, it is important that all related services should be stopped and the empty msdb and model DB is replaced with the copy that is saved at safe location.
2) Use the Template Master Database
In SQL Server 2012, the system database recovery criteria is a little different. There is template database that gets created at the time of installation. When the setup.exe is run for rebuilding the database, the template database is replaced over the database and transaction logs.
However, in case the msdb and model database are in working state, then it is a simpler job to just copy the template files to master database in spite of going for the long procedure of rebuilding the database and then restoring the msdb and model DB from backup. The template files are located at following location:
If master database backup is available, then restoration instruction can be checked out in section 1. But if there is no backup available, then section 3 will give an idea to deal with the problem.
#: The master database is unusable and there is no backup available. In this situation, what can be done is the master database can be reconstructed. For this, first of all connect to Server using SQL Server Management Studio and the admin account that was used while installation process or while rebuilding (in the above procedure). Here, you will observe that on expansion that databases tree is blank. This is because of the reason that master database stores the location info of all DBs which is now lost.
But it has to be understood that the database is still available, they are to be relocated. If the backup of the databases is there, then the job is easy, but if it is not, then the location of the database has to be recorded and attached manually. For this, the following T-SQL code can be used:
Note: It is necessary to change the database ownership as they must be owned by the account that has re-attached them.
Nevertheless, now a days, the idea of restoring system backups have taken a great turn in the way that the process has been simplified to a great extent. MDF file recovery programs for getting back the data from system databases have made restoration without backup very easy. The plus point is, for recovery of single database, others are not supposed to be involved like in the manual procedures. Most of the DBAs depend upon manual fixing in normal scenarios, but yes, these tools have made an impressive entry to deal with major issues in less RTO.
System Objects Restoration
On rebuilding the master database or on restoring it from the template, it is possible that some of the information like linked databases, login details etc. are lost. To get some of the objects back, you can take help of SSMS to create scripts. Although you won’t get the objects with details in exact match, but there is a fair possibility of getting very close to it.
