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?

Edited by pritaeas: Moved.

2 Years
Discussion Span
Last Post by amarbista

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. You may try read thes, it is a professional data recovery software. Here is page: http://www.techrepublic.com/forums/questions/how-to-fix-mdf-file/

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.