cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to recover log files to a rebuilt database?

JimDiaz
Participant
1,616

I have a remote SQLA 16 database involved in SQL Remote replication that is reaching the maximum 1TB size (4K Page Size, single DB Space) and want to unload and reload to alter the page size and number of DB Spaces. I've tested this and it all works fine, after setting the offsets I was able to receive and send a series of test replication messages.

The issue is it takes two to three days to perform the unload and reload on the hardware I'm working on and I don't want to disable a production system for that long.

I could re-extract but that would interfere with replication for the period of extraction, by procedure we shutdown all external activity on the consolidated when extracting.

Then I had this great idea! I'll unload and reload a backup then apply the transaction logs from the online database, switch the databases and I'm done. Problem is the logs don't apply to the rebuilt database.

Is there something I'm missing, should this work, is there a hidden switch I could use?

Accepted Solutions (0)

Answers (2)

Answers (2)

regdomaratzki
Product and Topic Expert
Product and Topic Expert

In v16, your only option is to apply transactions to a backed up and rebuilt copy of a database is to translate log files to SQL and apply them to the rebuilt database. This will almost certainly change the log offsets where operations were applied and the ending log offset of the transaction log, so it's nor replication/synchronization friendly.

Imagine the process below, which I think (i.e. I haven't actually tested this) will allow you to rebuild a backup of a v16 consolidated database, and while the backup is being rebuilt, you can still make changes to the consolidated database AND allow dbremote to run in RECEIVE ONLY mode, and SQL Remote will continue on it's merry way after the rebuild. It involves doing some unsupported magic that I will NOT explain on the forum, but I think this might work. I've simplified the process by assuming the database contains only cons.db and cons.log in the same directory, but it should easily extrapolate to your environment.

Jim/Volker/Breck : Can you see any issues with this process? The key takeaway here is that by running dbremote in receive only mode during the rebuild, the only updates to the SYSREMOTEUSER table will involve log offsets from the remote databases, not log offsets from the consolidated database.

  1. Stop dbremote on prod\\cons.db
  2. Stop engine hosting prod\\cons.db
  3. copy prod\\cons.db to backup\\cons.db
  4. rename prod\\cons.log prod\\cons.olg
  5. dbtran prod\\cons.olg ---> capture ending log offset of cons.log as ####
  6. start engine hosting prod\\cons.db
  7. start dbremote -r on prod\\cons.db ---> DO NOT SEND MESSAGES. RECEIVE MODE ONLY!
  8. rebuild backup\\cons.db
  9. dberase backup\\cons.log
  10. dblog -x 0 -z #### -ir -is backup\\cons.db
  11. Stop engine hosting prod\\cons.db
  12. dbtran -n apply.sql prod\\cons.log
  13. move prod\\*.* justincase\\*.* ---> Paranoid people are more likely to keep their jobs
  14. move backup\\cons.db prod\\cons.db
  15. dbisql -c dbf\\cons.db read apply.sql
  16. *** perform magic on prod\\cons.db so SYSREMOTEUSER matches SYSREMOTEUSER on justincase\\cons.db
  17. start engine hosting prod\\cons.db
  18. start dbremote on prod\\cons.db ---> Can send + receive now

Reg

regdomaratzki
Product and Topic Expert
Product and Topic Expert
0 Kudos

Not mentioned, but should be obvious :

  1. During the rebuild, don't do any administration of remote users. No new remote users, no new subscriptions, no remote resets. In a perfect world, there is nothing but DML being applied to the database while the rebuild is occurring.
  2. You should do this rebuild to a v17 database, so you can take advantage of dbunload -ao the next time.
VolkerBarth
Contributor
0 Kudos

Jim/Volker/Breck : Can you see any issues with this process?

Well, IMVHO, it seems reasonable... Of course, complex systems like SQL Remote setups have tought me to really test things besides assuming they will work 🙂

regdomaratzki
Product and Topic Expert
Product and Topic Expert

Edited my steps. The original steps would have left a log offset gap because I was executing dblog -x -z using the wrong offsets at the wrong time of the rebuild.

JimDiaz
Participant
0 Kudos

No don't think this will work I would think

8. rebuild backup\\cons.db
9. Stop engine hosting prod\\cons.db
10. move prod\\*.* justincase\\*.* ---> Paranoid people are more likely to keep their jobs
11. dbtran -n apply.sql prod\\cons.log
12. dbisql -c dbf\\cons.db read apply.sql
13. erase backup\\cons.log
14. dblog -x 0 -z #### -ir -is backup\\cons.db. ** Last active prod\\cons.log
15. move backup\\cons.db prod\\cons.db
16. *** perform magic on prod\\cons.db so SYSREMOTEUSER matches SYSREMOTEUSER on justincase\\cons.db
17. start engine hosting prod\\cons.db
regdomaratzki
Product and Topic Expert
Product and Topic Expert
0 Kudos

Did you comment based on my pre-edited version?

Running dblog -x -z after applying apply.sql was incorrect and would leave a log offset gap, but I think I addressed that in my edit.

JimDiaz
Participant
0 Kudos

Yes sorry I must have

VolkerBarth
Contributor

In my understanding you cannot apply logs from "different databases", and the rebuilt database is different from the original one.

Nevetheless, you might be able to translate the log of the original database to a SQL file and feed that (resp. the statetements done after the rebuild) to the rebuilt database. That should be quite easily doable as long as you prevent any SQL Remote activity after the backup copy is made. (So it would allow to work on with the old production database, which seems to be what you try to achieve.)

I do not know whether there are ways to allow SQL Remote activity in the "old database" after the backup copy is made, but certainly Reg will know.

regdomaratzki
Product and Topic Expert
Product and Topic Expert

Volker is correct, you cannot apply a transaction log from a different database to another database.

We solved this problem in v17 with dbunload -ao, which allows you to automate the process of rebuilding an online database against a backup copy of the database, and the applying the logs from the production database to the rebuilt backup, ignoring the "different database" problem, since we're in control of rebuild + applying of logs. This doesn't help you today at v16 though, because the initial database in this process must be v17. It does mean you'll only ever need to do this process once though, since once your DB is upgraded to v17, dbunload -ao will be a great feature for rebuilding large databases.

Reg

VolkerBarth
Contributor

Ah, wasn't aware of that big improvement... (yes, sometimes I'm missing those old SA blogs...)