Database Simplified Headline Animator

Database Simplified

Thursday 20 August 2015

Rebuild System Databases in SQL Server Failover Cluster Environment

 

This blog describes process of rebuilding system databases in clustered environment.

Sometimes an unexpected shutdown of SQL Server database server has resulted in crashing of the SQL Server or system databases. In this case when you will try to start SQL Server service, it will not start and if you will check the SQL Server error logs to check error message, you could see the following entries “Restore the database from a full backup, or repair the database”, “Cannot recover the master database”. means that corruption of master database.

So system databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases.

Limitation and Restrictions in Rebuilding System Databases

When master, model, and msdb system databases are rebuilt, the databases are dropped and re-created in their original location. Any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.

Now to get system databases back to its original position means all SQL logins and jobs, we need to restore last or previous full backup files of master, model and msdb databases. So it is very important to configure and schedule system databases backup also like other user databases in SQL Server.

Process for Rebuilding System Databases in Clustered Environment

In this blog, we are using SQL Server 2008 R2 failover cluster. It is a two node window failover cluster having four SQL Server instances running as shown below - 

Window Failover Cluster Configuration information -

               Node 1                                                                                  Node 2

Server Name – TESTSERVER_A                                            Server Name – TESTSERVER_B

Server IP – 10.123.23.1                                                          Server IP – 10.123.23.2

OS Version – Win server 2008 R2 (SP1)                                  OS Version – Win Server 2008 R2 (SP1)

SQL Server Version – SQL Server 2008 R2                              SQL Server Version – SQL Server 2008 R2

SQL Instances Running – SQLA, SQLB                                   SQL Instances Running – SQLC, SQLD

 

Here we will rebuild master/system database on SQL Instance named as SQLA running on Node TESTSERVER_A.

Steps to rebuild master/system databases are as follows-

Step 1.

First of all take RDP of cluster node named TESTSERVER_A using cluster administrator account which is current owner of SQL Server instance named SQLA for which you are rebuilding the master/system databases.

Step 2.

After connecting to node TESTSERVER_A, open failover cluster manager and then take cluster resources offline for SQL server instance named as SQLA for which you have to rebuild master/system database using setup.exe.

1

Step 3.

After taking cluster resource offline then SQL server instance service will stop but all disks associated with SQL Instance named SQLA will be available for this instance. In below screen shot, you can see that cluster Disk 7 and 9 are online.

2

Step 4.

Now we will rebuild master/system databases using setup.exe. We can find setup.exe file at following locations –

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

Step 5.

Open command prompt using Run as Administrator then type below command to rebuild system databases.

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2>setup.exe /QUITE /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQL$SQLA /SQLSYSADMINACCOUNTS="GGN-AM-19\User" /SAPWD=Password@123

Note – There are some parameter specified in above code-

a. SQLSYSADMINACCOUNTS - Local system administrator account which is GGN-AM-19\User.

b. INSTANCENAME - SQL Instance name which is default means MSSQL$SQLA.

c. SAPWD – Password of SA account.

Step 6.

After completion of command run in above steps, all system databases will be rebuilt.

Step 7.

Now you can check new created system databases files with current date and time at default location of SQL Server.

Rebuild_3

Step 8.

Now Start SQL Server services from configuration manager.

Step 9.

Now after starting SQL Services, connect to SQL Server using SQL Server Management Studio. Here all system databases are new and does not contain any details of all other user databases like SQL logins and jobs information.

Step 10.

Rebuilding the master, recreated the tempdb in the default location. If you are keeping tempdb in a different location perhaps on another set of disks then you need to move it.

Use below code to move tempdb as per your requirement.

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\tempdblog.ldf')

Note - Now master/system databases has been rebuilt. In next step we will see how to restore most recent full backup of system databases to get backup system database in previous state.

 

Process for Restoring System Databases in Clustered Environment

Once system databases are rebuilt using setup.exe as mentioned in above steps then all system databases (master, model and msdb) are dropped and re-created and any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.

So we need to restore most recent full backups of all system databases to get system databases back in previous state as these were before damage or crash happened to system databases.

We will restore master database on SQL Instance named as SQLA running on Node TESTSERVER_A.

Step 1.

After connecting to node TESTSERVER_A, open failover cluster manager and then take cluster resources offline for SQL server instance named as SQLA in which you have to restore master database.

1

Step 2.

After taking cluster resource offline then SQL server instance service will stop but all disks associated with SQL Instance named SQLA will be available for this instance. In below screen shot, you can see that cluster Disk 7 and 9 are online.

2

Step 3.

Now start SQL instance service in single user mode from command prompt. Open command prompt via run as administrator as shown below.

Use below code to start SQL Server in single user mode-

net start MSSQL$SQLA /m

Start-SQL_Single

Step 4.

Now after starting SQL server service in single user mode using CMD, we will connect to instance of SQL Server using SQLCMD from command prompt and restore master database as shown below.

Use below code to start SQL Server in single user mode-

C:\Windows\system32>SQLCMD -S SQLA\SQLA -E

Note – Here SQL Virtual Network name is SQLA and Instance is also SQLA.

After connecting to SQL server, restore master database from backup file using below code -

Restore Database master from disk='D:\SQL_DB_Backup\master.bak'
GO

Restore_Master

Step 5.

Now after restoring master database using CMD, SQL Server service will stop. Now restart SQL server service from SQL Server Configuration manager in normal mode.

Step 6.

Now we will restore msdb database. To restore MSDB database, no need to start SQL Server in single user mode. But make sure that SQL Server Agent service is stopped because by default it make one connection to MSDB database and prevent user to restore MSDB database via showing error that database is in use. Now restore backup of MSDB database from either command prompt or management studio.

If you found any issue in restoring MSDB database backup like database is in use then check using running command sp_who2 and find spids and kill those spids which are using MSDB database.

Use below code to restore MSDB database from command prompt.

Use master
GO
Restore database msdb from Disk='D:\SQL_Master_DBFiles\msdb.bak' with replace
GO

Step 7.

To restore model database, no need to start SQL Server in single user mode. Like user database, simply restore backup of model database from either command prompt or management studio.

Use below code to restore model database from command prompt.

Use master
GO
Restore database model from Disk='D:\SQL_Master_DBFiles\model.bak' with replace
GO

 

Troubleshooting in Rebuilding System Databases for SQL Server 2008/R2/2012

Sometimes, there may be some issue in rebuilding system database due to problem with files present in Binn\Templates folder. Once you check summary.txt file, let's say it is saying one message as follows:

Configuration error description: The file C:\Program Files\Microsoft SQL Server \MSSQL10_50.MSSQLSERVER \MSSQL\Binn\Templates\master.mdf is missing

In the above scenario, we have to copy file (master.mdf) from SQL Server dump or DVD into Binn\Templates folder and retry.

Note - Key Points to Rebuilding System Databases for SQL Server 2000, 2005, 2008, 2008 R2 and 2012

We can summarize the requirement to rebuilding system databases:

a. SQL Server 2000

· We can use RebuildM.exe

· Need setup media for system database files

b. SQL Server 2005

· We can use setup.exe

· Need setup media for setup.exe and system database files

c. SQL Server 2008/2008R2/2012

· We can use setup.exe

· Setup media not required and system database files are there in Binn\Templates folder