April 13, 2005 at 5:59 am
Hi,
i have a db for which .mdf and .ldf files were placed on different drives,the recovery mode is Full.
Sunday 10 am - Full db backup was taken
Monday 10 am - Differential backup was taken
Monday 11 am - Tlog backup was taken
Monday 12 am - Disk Crashed.
Can anyone give me the solution to recover the db till 12 am without losing any changes by users if the test cases are like this
a. .mdf is fully corrupted but ldf is proper.
b. .ldf is fully corrputed but mdf is proper.
thanksalot in advance..
nsr
April 13, 2005 at 9:34 am
For answer to a, look up "How to restore to the point of failure (Transact-SQL)" topic in Books Online for details. You should be able to recover all committed transactions to the point of failure.
Answer to b is in my opinion a bit more trickier. It depends on the state of the db at the moment of failure. I can see two approaches:
1.Recover db to the time of the last tr. log backup (11 am in your case) by applying your full backup, differential backup and then tr. log backup. You've got a clean, consistent data but you lose your work between 11 am and 12 am. This method is recommended and documented.
2.Your db has probably suspect status and is not accessible - set it to emergency mode and run DBCC REBUILD_LOG against your db, which is not documented by MS. It will create new log for you. Your database is now accessible but probably in inconsistent state. Backup your database files before setting db to emergency mode and run consistency checks afterwards. Now you could be lucky enough to extract some data (but with no guarantee).
The best approach in my opinion is to use the first method (possibly with combination of 2 to extract data and apply/view it manually). It brings to an attention the importance of transaction backups frequency. It depends on your requirements how much data you can afford to lose.
April 14, 2005 at 12:10 am
hi martin,
thanks for the reply.. so in either of these cases a and b (1,2) complete recovery of data is not assured right ? i mean the workdone between 11am to 12am.
Regards,
nsr
April 14, 2005 at 12:31 am
In fact, in case a) you take a transaction log backup immediately after failure occured (you don't need data files accessible) - with NO_TRUNCATE clause. Then restore last full backup and chain of diff., tran. log backups and as a last step you apply the above mentioned transaction log backup - so that you can roll forward all committed transactions to the point of failure.
For the case b, it's essential to place tr.log files on fault tolerant device (RAID 1 if possible) and chance that this kind of disaster occurs are minimized. But you're right - you must count on losing work since the last log backup.
April 23, 2005 at 6:06 am
hi rookie,
thanq for the reply...how can i take the log backup immediately after failure occured ? becoz the database is in suspect mode...?
nsr
April 24, 2005 at 1:13 pm
As long as your server is up and media that holds transaction log is intact, there is no problem doing that. You can easily test this situation for yourself with flash card. Create database - put your datafile(s) on the flash card, transaction log file on disk drive. Perform full backup of the db. Then create some test data in the db, then pull out the flash card. Now you are left just with transaction log file. Follow steps from the aforementioned BOL topic. As a last step recover database from backups to see that your data was not lost.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply