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.
