Mending the master database

Having a corrupt master database in SQL Server could be said to be one of the worst issues a database server instance could ever have.

What makes protecting against master database corruption more difficult is that it can’t have any of the regular high-availability features available in SQL Server used with it, such as database mirroring, replication or log shipping.  The only protection available is to back the database up regularly.

Backing up the master database

How you back that database up depends on your team’s skillset.  While SQL Server professionals would probably prefer to use native T-SQL commands and flat files if your infrastructure team only know about Backup Exec and would be responsible for restores at 2am then the decision might be made for you.

Below are links to the two backup methods mentioned above:

Ola’s T-SQL housekeeping and backup scripts here
Symantec’s Backup Exec SQL Server agent here

Restoring the master database using T-SQL

If your SQL Server master database is consistent but has invalid configuration settings then restoring it is fairly straight forward.

Using T-SQL and a flat file backup of the database requires SQL Server to be re-started in single user mode and the database restored, instructions can be found here.

If your SQL Server master database is corrupt to the extent that SQL Server won’t start in single-user mode then you have to rebuild the master database using the original setup tool.  A PSS blog article can be found here on how to do that.  If you’re rebuilding master on a SQL Server 2005 cluster expect to type one of the longest command line commands known to man, more details here.

Restoring the master database using Backup Exec

If you’re using a tool like Backup Exec, then restoring the master database becomes more straight forward according to Symantec’s web support.  It has a clever file system copying method for making a backup of master, meaning if you ever want to restore it whether because of corruption or invalid settings all you apparently have to do is stop SQL Server and rename its copies of the master mdf and ldf files to replace those you no longer want.  You then follow some post-rename steps once SQL Server is running again. 

An article about this clever feature can be found here.

Recovery not backup

As with all business critical systems how you back your systems up isn’t what matters, its how you test those backups and know how to use them in the event of a recovery which matters.  So don’t believe any of what I’ve written until you’ve tested it for yourself!

Advertisements

One thought on “Mending the master database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s