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:
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!