Clear all Your Doubts About SQL Database Recovery Terms

Recently, When I was participating in forums & Q&A sites then I noticed that lots of users  have so many doubts  about SQL Database Recovery terms. 

As we know in SQL Database recovery we have these three options which we use commonly:

• Restore from backup
• DBCC Command & Repair command or 
• Third Party SQL Database Recovery Software

So, I would like clear most of the doubts related to above three options:

Is “REPAIR_ALLOW_DATA_LOSS” command not cause data loss:  It all totally depends on corruption level. When you are using this command, you should make up your mind that you can also lose you data. That's why the option is named that - seriously.

Can we run repair without running DBCC CHECKDB: No, we can’t. Repair is an option to one of the consistency-checking commands (DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKDB

Note: DBCC CHECKFILEGROUP and DBCC CHECKCATALOG don't support repair

Is Repair fix every SQL Database corruption: No, it don’t fix, Its all depends on corruption level. There are some errors that DBCC CHECKDB cannot fix.  For more detail please check this post. 

Repair is safe to use on system databases: No, it’s not safe. We should never use it on master or tempdb because they cannot be put into single-user mode.  

Is 'REPAIR_REBUILD' resolves every SQL Database corruption: No it’s not true. REPAIR_REBUILD only fixes problem in nonclustered indexes. 

Is EMERGENCY mode repair always helps: No, it’s not repair. If there is something broken in the file-system than the command get fail. 

Can we roll back repair command?: It depends. You can roll back everything if you started an explicit transaction. But users do it very rarely.

Note: EMERGENCY mode repair can never be rolled back.

Is SQL Database Recovery Software safe to use: YES, when you are using this software, you should first make a backup of your database. I always first preferred restore from backup solution so I will also recommend you it as a first step. You should only use this option when you don't have backup.  

Before using any recovery step, you should be properly aware about their pros & cons.  It will help you to choose better solution to recover your corrupt SQL Server database.