If the master database fails, Microsoft SQL Server can be brought to its knees. See how to recognize this event and learn the steps for recovering the master database using the Enterprise Manager and the Query Analyzer.

As a Microsoft SQL Server administrator, you must know how to recover a corrupt master database. The master database stores your logins and, most importantly, the pointers to all of your databases. Without the master database, you can't successfully start SQL Server. I'm going to walk you through the process of recovering the master database in the event of corruption and show you how to rebuild the master database, if necessary.

Have a plan

It is important to have a plan for dealing with the corruption and/or failure of your master database. That will help you follow a methodical approach when disaster strikes, rather than acting too quickly under pressure. I have been in many situations where it would have been easy to panic, but I've managed to weather the storm by remaining calm and following the proper methodology when dealing with a problem.

In SQL Server 2005, we introduced a different method than in previous versions to rebuild system databases (affectionately known as "rebuild master"). You were required to use the setup.exe program with command line switches.

This is no different in SQL Server 2008 but the command line switches have changed some and the process behind the scenes to rebuild the system databases (master, model, and msdb) is also a bit different.

Currently the SQL Server 2008 Books Online only mention an option for setup called /REBUILDDATABSES (See this link) but this information is not correct so I'll outline how to do this here in this blog post. We will also get our documentation updated to reflect this information.

The syntax for using setup.exe to rebuild the system databases is as follows:

setup.exe
/QUIET
/ACTION=REBUILDDATABASE
/INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS= accounts
[/SAPWD=password]
[/SQLCOLLATION=collation_name]

Here are the details about how to use this syntax and how it works:

Hello Folks,
You might have seen my last blog post, which was a brief introduction about DBCC CHECKDB. If you want to refer it again, then please check the link;

Well I have concentrated this blog-post mainly on how to repair the database Using DBCC CHECKDB.

I have made some keynotes about it:

• If there’s an error and DBCC CHECKDB can fix it up, then DBCC CHECKDB indicates the repair level that is needed to repair the specific errors.
• Most of the times if the error is reported by DBCC CHECKDB, it is recommended to restore the database from a known good backup.
• If there’s happen to be no good backup, then you have to use the repair option with DBCC CHECKDB.
• You should also keep in mind that repairing the database is a separate operation from the normal DBCC CHECKDB because the database needs to be placed in a single user-mode with the ALTER DATABASE command before a DBCC CHECKDB can be executed with the REAPIR option.
• So now if you want to place AdventureWorks2008R2 sample database in a single-user mode, then write the code:  

Issue

You have a database in MS SQL that is tagged as (suspect) and you are unable to connect to the database.

Possible Causes

• The database could have become corrupted.
• There is not enough space available for the SQL Server to recover the database during startup.
• The database cannot be opened due to inaccessible files or insufficient memory or disk space.
• The database files are being held by operating system, third party backup software etc.
• There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

Resolution

These steps require you to have Microsoft SQL Server Management Studio installed on your computer.  If you do not have this installed please follow the steps outlined in the following article: How To Connect To Your MS SQL Database

Always back up the website before making any changes to the database . Shared hosting customers can do this through the Control Panel. Refer to Back Up Your Website Using Plesk. Dedicated server customers can back up the site either through the Control Panel, or through the Control Suite. Refer to How to Back Up a Domain Using Control Suite.