SQL DataCompare to restore data lost on server from backups

kinano

I have the following scenario for which I'd like to pick your brains a little.

We have a DB server that had performance issues on Friday. The db server was mirrored. We had originally switched the mirror server to become the online master. However, we discovered that the mirror server had some discrepancies with the master server in terms of schemas. After a few hours, we had to restore the master DB server to a stable state from the full backups taken on Friday just before the lag was observed.

Hence, we have potentially lost some of the data collected after Friday.
I am thinking we could use SQL Data Compare to get the differences in Data between the restored master DB server and the latest backup files we have in place and hence restore as much data lost after Friday as possible.

My questions follows:
It seems that SQL Data compare supports comparing db objects in an online database to a backup file directly with no problem. Would the comparison work only for full backup files or can we also use transaction logs backup files to make the comparison in increments if needed?

I realize you may need more information to answer this. Please let me know if you have any questions.

James B

Thanks for your post - if you need a quick answer it's usually best to email support@red-gate.com directly, as we're only alerted to unanswered posts automatically after a day or so.

As for your query, SQL Data Compare only works with full backups, so if you need to access the data from later transaction logs, I guess you'd need to restore the database + logs to a new temporary DB and then work with that from within Data Compare.

Hope that helps,

James.

kinano

Makes perfect sense. Thank you for the reply.