Friday, January 1, 2010

Rebuild and Restore Master database

How do you know if your master database is corrupt?

Let's pretend that your company had a power surge and your SQL Server rebooted. Upon reboot, SQL Server would not start. If you check the error log , you'll see that the master database is either corrupt or missing.Rebuild your master database:
Your first step in recovering your master database is to use the Rebuild Wizard (Rebuildm.exe), located in the \Program Files\Microsoft SQL Server\80\Tools\BINN directory. Let’s walk through the Rebuild Wizard to see how it works.Start by double-clicking Rebuildm.exe to bring up Rebuild Master screen.
On this screen, you can specify the collation settings of your database server and the location of your data files during your original install. To make the latter easier and faster, copy the x86 directory from the SQL CD to your hard drive and point to the local copy. Once you have verified all of this information, click Rebuild. You'll then be prompted to confirm the operation,
Click Yes. Once the process is completed, you'll see a message telling you that the rebuild was successful. You now have a brand new master database and are ready to restore your master database.

Restore Master db with a Backup file:First, start SQL Server in single-user mode by opening up a command prompt and issuing the command sqlservr.exe –c -m from the \Program Files\Microsoft SQL Server\MSSQL\BINN\ directory.

After you start SQL Server in single-user mode, you can restore your master database from a backup. You can restore it using either the Query Analyzer or SQL Enterprise Manager.

If you're using Enterprise Manager, right-click on the master database, choose All Tasks Restore Database, and browse to where your device is located, . Click OK twice, and you have successfully restored your master database.
Once you've restored your master database, exit single-user mode and restart SQL Server in normal operation mode.If for some reason your restore operation does not work, you can try an alternative method. Simply rebuild the master database and attach all of your databases that reside in the data directory. You can attach the databases using Enterprise Manager or Query Analyzer. In Enterprise Manager, right-click on Databases and choose Attach Database.

No comments: