I have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, and I have a query to find which records should be there but aren't, so I want to go through and run the query on each backup. The backups are fairly large, but I only need 2 tables out of the whole database for the query. What would be the most efficient way to do this over more than a month of backups?
My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.
Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases.
If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them.
Example, restore the oldest backup, if it contains the records, go the the backup in the middle, if that doesn't contain the records take the backup between those backups, etc etc. It's still tedious work, but at least you wouldn't have to restore them all.