Wednesday, February 15, 2012

FAQs for SQL Server Database Corruption

Question: Does a missing file (.mdf) send a database in suspect mode?
Answer: NO. It may have true with the earlier (SQL 2000 & earlier) versions of SQL server but it is not true in current versions. Your database may be inaccessible but not in suspect mode in case of missing data files.

Question: How many tools are available to check the database integrity?
Answer: CheckDB is the only available tool to check the database integrity.

Question: How to bring database online from suspect mode?
Answer: If you database has gone in suspect mode then you are not able to do any operation on the database. To bring database online, ALTER Database Set Emergency. Once your database in emergency then RUN CheckDB with repair_allow_data_loss on the database and bring online.

Question: Do you loss data after running CheckDB with repair_allow_data_loss?
Answer: The short answer is YES. As name suggests, you will loss some amount of data after running CheckDB with repair_allow_data_loss.

Question: What are options available in CheckDB tool?
Answer: There are two repair options available in CheckDB tool. One is Repair_Rebuild and another is Repair_Allow_Data_Loss.

Question: What are the reasons for database corruption?
Answer: There are ton of reasons for database corruption. Some popular reasons are hardware failure, virus attack, power failure, meta-data structure corruption etc.

Note: 99% SQL database corruption happens due to faulty hardware.

Question: What to do after database corruption?
Answer: Don't panic; if possible take backup of corrupt database.

Question: How to recover corrupt database?
Answer: It is a very brought question but we can classified it in three simple steps.
  1. If backup is available then restore corrupt database from backup.
  2. Run CheckDB on the database, see the SQL server error log & Run CheckDB with repair_allow_data_loss. 
  3. Try any third party MS gold partner's SQL recovery software.

Question: Which is the best third party SQL recovery software?
Answer: The short answer is Stellar Phoenix SQL recovery software. This recovery software recovers and repairs.mdf and.ndf files of SQL server. It supports all latest version of SQL server including SQL Server 2008 R2, 2008, 2005, 2000 & 7.0.

Question:Does simple recovery model store log files?
Answer: No, Simple recovery model does not store log files of SQL server database but full recovery model stores all the log records of SQL server database.

No comments:

Post a Comment