Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Disaster Recovery: Rebuild system databases on SQL Server 2008 cluster

By Basit Aalishan Masood-Al-Farooq,

Introduction

Not long ago I was called at night by an operator because our company had experienced a power surge. As a result of this, all our SQL Servers rebooted. But upon reboot, one of our mission critical clustered SQL Server 2008 R2 instances failed to start automatically. Upon checking the error log, I noticed that following error appeared in the application error log:

During redoing of a logged operation in database 'master', an error occurred at log record ID (206:424:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

After doing a little more digging, I noticed that the file system of the drive that hosts the system databases files was corrupt. In order to meet the client's SLA I had to bring the SQL Server online within one hour. My first thought to recover system databases was to restore them from backups on backup drive, but unfortunately the backup drive was also corrupt as part of power failure.

I was left with two options: either restore the system databases from backups on tape or rebuild the system databases from the SQL Server 2008 installation media, which we have onsite. I quickly came to the conclusion that I had to rebuild the system databases. This was the most suitable solution for this situation because our tapes were stored on a DR site that is in a different city, and it would take at least 3 hours to get the tapes. Also by the time I finished rebuilding the databases, I would have my backup drive available because the maximum time for recovery for a backup drive is 1 hour.

In this article I will demonstrate you the steps I performed to rebuild all system databases (except resource database),and  then I will show you the steps I used to rebuild the resource database. In the end I will list the steps I performed to bring server back to the state before failure.

Step 1: Rebuild the system databases (except resource database)

The first step of the recovery process is to rebuild the following system databases i.e. master, model and msdb. I performed the following steps to rebuild master, model and msdb.

I first logged onto the active node and located SQL Server 2008 R2 installation media on the server. Using Cluster Administrator, I took all the SQL Server resources offline in the corresponding cluster application group. From a command prompt window, I used the following command to start the SQL Server setup to rebuild system databases:

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

The table below explains the parameters for the Setup program:

Parameter name

Description

/QUIET or /Q

Specifies that Setup run without any user interface.

/ACTION=REBUILDDATABASE

Specifies that Setup re-create the system databases.

/INSTANCENAME=InstanceName

Is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER.

/SQLSYSADMINACCOUNTS=accounts

Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role.

[ /SAPWD=StrongPassword ]

Specifies the password for the SQL Server sa account. This parameter is required if the instance uses Mixed Authentication (SQL Server and Windows Authentication) mode. 

[ /SQLCOLLATION=CollationName ]

Specifies a new server-level collation. This parameter is optional. When not specified, the current collation of the server is used.

The command I executed to start the SQL Server setup is shown in the figure below:

When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages as shown in the following figure:

I examined the Summary.txt log file as show in the following figure to verify that the process completed successfully:

This file is located at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Logs.

Step-2: Rebuild the Resource database

The second step of the recovery process is to rebuild the Resource system database. I performed the following steps to rebuild the resource system database:

I launched the SQL Server Setup program (setup.exe) from SQL Server 2008 R2 distribution media. In the left navigation area, I clicked Maintenance and then clicked Repair. The setup support rule and file routines ran to ensure that my server has prerequisites installed and my server passes setup validation rules. I clicked OK to continue. On the Select Instance page, I selected the instance to repair, and then clicked Next to continue. Now repair rules ran to validate the operation. I clicked Next to continue. From the Ready to Repair page, I seleted Repair option and then clicked Next to continue. The Complete page appeared that indicates the operation is finished successfully.

Step 3: Post-Rebuild Tasks

The last step of the recovery process is to bring the server to the state before failure. I performed the following steps to accomplish this task: 

After rebuilding the resource database I re-applied all service packs and hot fixes. I restored the master, msdb and model databases from the most recent backups. I bring the SQL Server instance resources online in Cluster Administrator.

Conclusion

In this article you have seen how I rebuilt system databases in a SQL Server 2008 cluster that failed to restart. You should rebuild system database only under conditions when you don’t have backups of system databases, system backups are corrupted or backups are not immidiately available. You will lose all your Linked Server, Logins, SQL Server Agent jobs when you rebuild system databases. To restore Linked Server, Logins, SQL Server Agent jobs either restore the master and msdb databases from most recent backups or recreate these objects by executing the scripts containing the definition of these objects. 

Total article views: 4855 | Views in the last 30 days: 21
 
Related Articles
FORUM

Setting Up TFSWarehouse database and rebuilding Team System Cube

Setting Up TFSWarehouse database and rebuilding Team System Cube

ARTICLE

Rebuild SQL Server Master Databases in Less than 5 Minutes

How to rebuild your master database quickly and safely.

FORUM

Server Database Backup

Server Database Backup

FORUM

Full Server Rebuild

Full Server Rebuild

FORUM

how to take DataBase Backup from remote server to local system

how to take DataBase Backup from remote server to local system

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones