SQL SERVER How to repair a database

Corrupted database

SQL SERVER database corruption is a common problem. Few things are responsible for database corruption. sometimes corruption occurs within typical data pages that store the actual data needed by your database to keep it running and viably storing data. Or, at other times, corruption might simply occur within an index or other simple structure that is effectively an ‘ancillary’ feature of your database.
Sometime the corruption may be fatal and leaves no option to repair.

When will you realize that your database is corrupted?

Most of the time you will get this message when you will try to data from some specific table (Actually the table is corrupted). And sometimes you will get different error. Like this

 What should i do , when i'd realize that my database is corrupted?

If you have recent backup then try to restore the database. Otherwise your have to repair the database.

First try the safe mode for repair database. Open SQL Query Analyzer and write down the code below.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Write your database name in the place of <Database name>. If your database name is "PMS" (This one is mine database name) then your code should be 

 DBCC CHECKDB (PMS) WITH NO_INFOMSGS, ALL_ERRORMSGS

Before run this code I will suggest you 2 things. Try to avoid single cotation mark before and after database name. And second thing is make sure that your selected database is not the database that you want to repair. You can select any default database. I will suggest you to select "master" database.
You can avoid both of these suggestions. Most of the time you will not get any error. So it is not mandatory.

If your corrupted database have some minor problem then repair will be done easily. If not then you will get some error, something like this

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PMS )

What should I do if my corrupted database require hard repair, that can causes data loss? 

To do so you , first you have to set SINGLE USER mode for that particular database. To set single user mode type down the code below and select the code and run(F5). If you don't apply single user mode and run the repair code you will get an error like this

ALTER DATABASE PMS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

MULTI_USER mode won't allow you to hard repair to your database. Single user mode means, only one connection can be used. When you  are using the database no other user will be allowed to access the database that time.

Now write the code below and run (F5)

DBCC CHECKDB  ( PMS , REPAIR_ALLOW_DATA_LOSS  ) WITH ALL_ERRORMSGS 

Remember here PMS is my database name. Replace it with your own database name. It will take some time to run the query.When the repair is done you will get some message like this.

And thats all you needed. One more thing. Before you close all remember to set the multi user mode again. To set multi user mode again write down the code below and run (F5)

ALTER DATABASE PMS SET MULTI_USER WITH ROLLBACK IMMEDIATE

Thanks.

See this tutorial on youtube 

SQL SERVER How to repair corrupted database?

All the codes are here.

DBCC CHECKDB (PMS) WITH NO_INFOMSGS, ALL_ERRORMSGS

go

ALTER DATABASE PMS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

DBCC CHECKDB  ( PMS , REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS 

go

ALTER DATABASE PMS SET MULTI_USER WITH ROLLBACK IMMEDIATE