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.
