Recover from data and log files

Last Post 16 Feb 2013 07:30 PM by cookice2013. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
Topic is locked
Author Messages
BobHoward
New Member
New Member

--
19 May 2005 12:01 PM
My SQL2000 server crashed, and I was able to recover the data and log files. I had to reinstall from scratch after that. I found that I can use the sp_attach_db procedure to create databases on the new server from the data and log files. That recovery procedure worked, at least so far. But it's left me with a lot of questions.

Is this a valid recovery? Will I be missing data, other than the obvious uncommitted transactions? Why isn't this documented as a recovery procedure? The BOL on sp_attach_db says it should only be used after a detach, which wasn't the case here. If this works, why bother with traditional backups at all? Why not just back up the live data files?
BobHoward
New Member
New Member

--
19 May 2005 02:35 PM
Thanks for the detailed response. Are you saying, though, that some data from a committed transaction might not have been written to disk? That would seem to be contrary to the ACID properties. Or are they written to disk, but in some intermediary file other than the .mdf datafile?
pyale
New Member
New Member

--
20 May 2005 08:51 AM
Data changes are guaranteed to be written to the transaction log, but the data pages may still be held in cache. If every transaction had to write affected pages to disk on commit, the disk throughput and cache turnover would be so high that the database would be very slow indeed. The fact that the transaction changes are recorded in the transaction log means that a transaction can always be rolled back if necessary, even if the server crashes. This is why the transaction log is always scanned for transaction to be rolled back / rolled forward on server startup before a database is brought online.
BobHoward
New Member
New Member

--
20 May 2005 09:33 AM
That makes sense, thanks. It sounds like the data and log file are in synch with each other, and contain all the committed transactions between them. Does that mean if you recover both the .mdf and .ldf file from a crashed server, and attach those on a new server, you're guaranteed a full and consistent recovery? (Except for uncommitted transactions, of course.) And if so, the real question is, why isn't backing up the live .mdf and .ldf files considered a "proper" backup and recovery tactic?
BobHoward
New Member
New Member

--
20 May 2005 01:13 PM
Why would you, if your backup software can deal with open files?
BobHoward
New Member
New Member

--
23 May 2005 10:48 AM
I happen to agree, but we're moving away from my point. Really what I'm after is whether using "live" copies of .mdf and .ldf files is acceptable for recovery. I think the conclusion is that it's workable, but there are risks. Best as a last-resort recovery, probably.
cookice2013
New Member
New Member

--
16 Feb 2013 07:30 PM
You may try iDisksoft Data Recovery, it is a professional data recovery software.
Here is download page
http://www.idisksoft.com/
Topic is locked

Acceptable Use Policy