Categories
Sql Server

How to recover corrupt Master Database

Master Database Physical Structure

How to recover corrupt Master Database

Hi friends, in this post we are discussing a very important task played by a SQL Server DBA How to recover corrupt Master Database. In case of MASTER database corruption in SQL Server, you can’t able to start your SQL Server. So it is a wise approach to take backup of your system database’s as well with user database’s.

Master Database: Master Database contains all system level information for an instance of SQL Server.

Physical Structure:

Master Database Physical Structure

Recover Corrupt Master Database Method 1 (From Backup):

Recover from a valid database backup, it is very useful to keep a latest backup of your system database. System databases can be restored only from backups of same version of SQL Server that the server instance is currently running.

To restore master database, Start the server instance in single-user mode (Start SQL Server in Single User Mode).

To restore a full database backup of master, use the following:

RESTORE DATABASE master FROM disk=d:\master.bak  WITH REPLACE

C:\> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE;
2> GO

Restart the server instance

For a named instance, the sqlcmd command must specify the -S<ComputerName>\<InstanceName> option.

Recover Corrupt Master Database Method 2 (Rebuild Database):

Rebuild your Master database with SQL Server Installation media.

The following procedure rebuilds the master, model, msdb, and tempdb system databases. You cannot specify the system database to be rebuilt. For clustered instances, this procedure must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.

1. Insert the SQL Server 2016 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release.

2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. The command prompt must be run as Administrator.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS
=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

3. When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages.

4. Rebuild Database scenario deletes system databases and installs them again in clean state. Because the setting of tempdb file count does not persist, the value of number of tempdb files is not known during setup.
Note: Method 1(From Backup) is best available method to recover Master database, in Method 2 you will loose all your configuration settings etc.