How To Rebuild The master Database :

If your SQL Server master database becomes corrupt, such as from its disks losing power while SQL Server was running, the conventional advice is to rebuild the master database, start SQL Server, then restore the backup of the master database. That’s because SQL Server won’t start with a corrupt master database. But rebuilding the master database is usually quite time-consuming and frustrating, with a substantial amount of trial-and-error, especially if it’s on a cluster. It can be so troublesome, a lot of people consider reinstalling SQL Server from scratch!

Instead, you can restore a backup of the master database on another instance of SQL Server as long as it’s the same version of SQL Server, then use the restored files to replace the corrupt files on the broken system.

Steps : Restore your master database backup to another instance of SQL Server and in the “To database:” section of the Restore Database dialog box, use an alternate database name such as “recover_master” to avoid conflict with the master database on this instance of SQL Server. This will cause the mdf/ldf to be named “recover_master.mdf” and “recover_master_1.ldf”. Then detach the recover_master database, go into the file system, copy the recover_master.mdf and recover_master_1.ldf to the instance of SQL Server with the corrupt master database. Delete the corrupt master.mdf and mastlog.ldf files, rename recover_master.mdf to master.mdf, and rename recover_master_1.ldf to mastlog.ldf. Now you’re ready to try starting the SQL Server service again!

Because the master database is usually very small, this method should just take a few minutes, it should work for all versions of SQL Server, and it works for both stand-alone instances and clustered instances.

IMPORTANT: The version of SQL Server used for the restore has to match that of the damaged instance. For example, if you restore a database backup from SQL Server 2005 on SQL Server 2008, the restore process will modify the structure of the file, and you won’t be able to use the restored mdf/ldf on the older version of SQL Server.

By the way, if you’ve ever experienced pain in rebuilding a master database, and your reaction to this post is “why didn’t I think of that?”… it could have been because you were under a lot of pressure to get the system working again, etc.

Think of how bad off you’ll be if you don’t have a backup of your master database: You’ll be wasting a lot of time recreating logins, roles, database mappings, and setting permissions — all of which could be avoided with a simple backup. And test restores not only validate the quality of your backups, they also give you valuable practice so the first time you perform restores isn’t while you’re under the stress of a real disaster!

Advertisements

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