Recover Damaged Database From Data File
Recover Damaged Database From Data File
By : Kasim Wirama, MCDBA
This article will show you how to recover database if your backup is not considered valid by SQL Server engine by saying that your database is not clearly shutdown.
Typical action you need to do is to restore from full backup and subsequent transaction log backup. Usually this approach works well until you meet some condition that you cannot use your transaction log backup.
If you cannot restore transaction log backup, your last chance is recover directly from data file (mdf file), but it doesn’t guarantee database consistency, but still you get your latest data instead of your recover from last full backup.
This is unconventional approach, and it should be used as last effort if your database cannot be recovered with typical restore.
First you create database with same size, logical and physical name of data file. Next step is that you shutdown SQL Server, override the new data file with the old data file (valid one) and start the SQL Server.
After step two is done, your database will be in suspect mode, turn your database mode into emergency and single user only with ALTER DATABASE command.
Run DBCC CHECKDB ( your database name, REPAIR_ALLOW_DATA_LOSS) command. After that, bring online your database, and set your database mode to multi user with ALTER DATABASE command.
Now your database has been recovered, don’t forget to do full backup soon after your database is online.