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:
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER;
- The above code would wait indefinitely if there is a lock on the database or users are connected to the database. So to overcome this situation, use the code below:
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
- If the AUTO_UPDATE_STATISTICS_AYSYNC option for the database is ON, then you will be unable to place the database in single-user mode because the background thread that is used to update the statistics takes a connection against the database.
- The DBCC offers two repair modes:
- REPAIR_REBUILD: This performs a repair that does not lead to any data loss.
- REPAIR_ALLOW_DATA_LOSS: This performs a repair and fixes to the corrupted database structures, and also results in data loss.
- The following example will let you understand the whole concept, where I have used DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option;
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; BEGIN TRANSACTION; DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;
The result can be seen as:
It’s the best practice if DBCC CHECKDB asks you to use REAPIR_ALLOW_DATA_LOSS, then take a full database backup first and then run DBCC CHECKDB with the repair option in a user transaction.
You can also repair it in Parallel, i.e. in a Multi-User. Since running DBCC CHECKDB in parallel is processor intensive. So you can disable it, if it is against your environment or situations with the help of trace flag 2528.
Well this was all about repairing database with DBCC CHECKDB.
And also comments on this!!
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
9 Comments on “Repair the database using DBCC CHECKDB”
You have done a brilliant job. I had read several articles on “DBCC CHECKDB with repair_allow_data_loss” but your article is specific because you have explained with detailed example. I want to add one more point in your article that is 3rd party MS Gold certified SQL database recovery tool, its repairs corrupt SQL server database even if DBCC CHECKDB fails to perform database recovery. As SQL server suggests that you will loss some amount of data after using DBCC CHECKDB with repair_allow_data_loss so for complete recovery of database, you should get help from any 3rd party sql recovery tool.
I have an TMSDATA.MDf database which I can’t attach due to some corruption. How can I check and/or repair it so that I can attach it.
Nicely described step by step process with good examples. I also want to share that SQL Recovery Software repair database corruption at both file system level and database level. It enables you to correct all consistency errors reported by DBCC CHECKDB and recover all inaccessible information of your corrupt database.
Nicely described. Thanks for sharing !
I just want to share that there are some situations when built-in DBCC CHECKDB utilities get failed to resolve the corruption issues in SQL server databases. In such case, a third party tool may help the users to get their data back. Like us, we repaired and rebuilt our corrupted SQL server databases with the help of this globally used program: http://www.serversdatarecovery.com/sqldatabase.html
A free trial version is also there !
Thanks for this post. It helped me a lot.
please i need a tools to repair my corrupt sql database. i just recover the sql from Ramsomeware but trying to attached it to my database but Sql management saying the data is in recovery mode wait for recovery to finished.
i need help. firstname.lastname@example.org
Thanks for this post. It helped me a lot.