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?

I think you have never thought of crash situation earlier. You started hunting when you had disaster. There is tons of content about high availability and disaster recovery. You just need to search for right terms. Whenever data is critical, the HA and DR plan is made.

Your procedure is not guaranteed to work. I must say you were lucky that you were able to recover MDF and LDF file and use them.

You can't backup MDF and LDF files when SQL Server process is running. That's why you have backup.

BTW, SQL 2000 is 15 year old product and it not even supported by Microsoft Support. You should plan for upgrade.

Yes 2000 is outdated, there will be few server which clients don't want to upgrade even though MS is not supported.

For those we need to have HA/DR in place always.

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/

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/

• Proposed as answer by soniajazon 19 hours 58 minutes ago
• Unproposed as answer by soniajazon 19 hours 58 minutes ago
• Marked as answer by niklauslekas 19 hours 57 minutes ago