FAQ: SQL Server Disaster Recovery

Q: Can I restore a SQL server database if I have data & log files?

A: Yes, you can restore a SQL server database if your data file & log file is not corrupt. You can do this by attaching data & log files to the SQL server.

Q: Can I restore a SQL server database if the data file is corrupt?

A: No, you cannot restore a SQL server database with a corrupt data file. You need to restore your database from a good known backup. If you don't have a good backup of your database, there is less change that you can do. There are some data recovery companies that claim they can recover data from the corrupt file by repairing the file. So you can go to the Internet and search the best SQL server mdf file recovery software and see what they can do for you.

Q: Can I attach a SQL server database without transaction log file if database was cleanly shutdown?

A: Yes, you can attach a SQL server database without transaction log file (.ldf) of the database. You can attach your database with the help of SQL Server Management Studio (SSMS). MS SQL server will create a transaction log file while attaching the database.

Q: Can I attach a SQL server database without transaction log file if database was not cleanly shutdown?

A: Yes, you can but method will be different. To do this, you have to perform below steps:

• Create a same size database as your previous database size
• Shutdown SQL server
• Change the old mdf file with new created mdf file
• Start SQL server, database may go in suspect mode
• Change the database mode from suspect to emergency by ALTER command
• Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS.

Q: Is there any In-built utility in SQL server to repair corrupt file like Compact & Repair in MS Access?

A: Yes, MS SQL server has DBCC CHECKDB that performs almost similar activities as Compact & Repair does. DBCC CHECKDB has three options; repair_fast, repair_rebuild & repair_allow_data_loss.

Q: Can I loss data after running DBCC CHECKDB with repair_allow_data_loss?

A: Yes, you may. As the name of command suggests you may loss the data from your database. In some cases it has been observed that database administrator has not lost the data after running DBCC CHECKDN with repair_allow_data_loss command against the database.

Q: Can I backup transaction log if my database is using Simple recovery model?

A: No, you can't backup transaction log file when you database is in simple recovery model.

Q: What can I do to avoid data loss from corruption?

A: Backup, Backup & Backup. It is the best solution to avoid any data loss from corruption. Additionally, running DBCC CHECKDB command against the database on regular interval is a good habit. You may know the corruption as soon as it occurs.

Q: What is the best time interval to run DBCC CHECKDB?

A: It depends upon your business need. If you can afford 2-3 days database downtime then run it on monthly basis but I don't think so a company can afford 2-3 days database downtime.