0

I have got two files, test.mdf and test.ldf, with sizes around 200 Mb and 200 MB.

I restore the database using "Attach Databases" options, all is ok, but I found that there are a lot of missing rows in some tables.

What can I do?

I have all files from previous windows installation, I read that mdf has got uncommited changes, where are those changes in the files?

1 Answer 1

1

Any rows you'd be missing would have been inserted into the original instance of the database after you copied the MDF / LDF files (or after whichever copy of the MDF / LDF files you've located since you're saying you're missing over months of data and this happened after a crash). So it depends on when that version of the MDF / LDF was last modified / copied.

If you have Full Backups and Transaction Log Backups setup on the original database, you can use a backup restore to get as close as the frequency that you Transaction Log Backups run (e.g. 5 minutes granularity if that's what you schedule them for). And then if for some reason you don't restore it right away and the next Transaction Log Backup(s) run, you can always add the new Transaction Log Backups to your list of backups to restore to keep you current.

Or you can even restore all the backups you have with the NORECOVERY option (which will leave it in the recovering state) if you don't plan to bring it online right away but do want to keep it current to the original database. Then as more Transaction Log Backups are created on the original database, you can use those backup files to roll forward the copy of the database until it's at a point for which you want to bring it online. Once brought online, no more Transaction Log Backups can be applied without doing another full restore from all the previous backups to the nearest Full Backup.

9
  • No, there server crashed, an there are missing data of months of work, i copied the mdf and ldf with the server dead using the windows recovery mode. I havent backups files of course, I only have got the original mdf and ldf. Jan 25, 2022 at 12:25
  • Where are those "Transaction Log Backups"??? Jan 25, 2022 at 12:26
  • @dlopezgonzalez It sounds like you didn't copy the correct instance of the MDF and LDF files then (maybe they were an old version you copied). Do you guys have database specific backups setup (as my answer talks about)?...as that would be important and typically the correct way to recover from a crash.
    – J.D.
    Jan 25, 2022 at 12:27
  • @dlopezgonzalez If you guys setup backups, the Transaction Log Backups would be wherever you guys specified for them to be created to. Generally they end with the ".trn" file extension (though they don't have to if someone chose a different extension when setting them up). You'd also need the nearest Full Backup file to be able to restore the Transaction Log Backups. The Full Backup typically ends with the ".bak" file extension (though again that can be changed as well). If you have access to the old server, you can see if a Maintenance Plan or SQL Agent Job were setup to take backups.
    – J.D.
    Jan 25, 2022 at 12:30
  • I have not backup, i have got only files from sql server 2008 installation (included mdf and ldf). Jan 25, 2022 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.