I had a hard-drive failure recently where I lost the database and transaction log.
Unfortunately we did not have a robust backup strategy and the most recent backup was about a week old.
I'm wondering the best strategy to recover the database, and the missing weeks worth of data, by using the remote database(s) and/or transaction logs. * The main problem is that most of the remotes had replicated data during the last week of the database's life and therefore have confirmed received offsets that are newer than what the restored database expects. (DBREMOTE reports "Message does not belong to me...")
We only have 7 or so remote sites, which have similar but different subscriptions. So its not like I have to reconcile hundreds of remotes, however I do not have direct access to the databases but could setup a remote connection if necessary.
We are using SQL Remote 8.03 over FTP. There were multiple clients that had run dbremote between the date of the old backup (July 25) and the date of the database crash (July 30).
I know that I could use the "old" backup and then re-extract all the clients, which would get all the offsets in sync, however that would mean that a weeks of data would be lost.
Should I use the remote transaction files and extract the SQL ?
Is there a way to use the FTP messages on the server that were not picked up to restore data ? (some clients had not replicated in a while)
I am not averse to using undocumented procedures to reconcile the expected sent/confirmed etc values.
asked 05 Aug '11, 12:54
I don't think there are any automated ways to recover the consolidated database from all the remote databases even after you fixed all the offset problems, because dbremote will not send any operations back to the subscriber, if the operations originally were coming from the subscriber. However, you may be able to recover part or all of the data from the transaction log files of all the remote databases manually, assuming the transaction log files of the remote databases have not been deleted. Here are the steps:
Then repeat steps 2) to 5) for each remote database. After you have the my.sql files from all the remote sites, you may need to check if they contain any duplicate data. If there are not, then apply all my.sql against the consolidated database and then re-extract all the remotes.
Please try these steps on the test environment first.
answered 05 Aug '11, 15:26