0

This might seem like a stupid question. Can i restore logs of one database to another existing database of same schema.?

I have a database d1 in Server A.I took backup of that and restored as database d1 in another database server B Then in server B i merged all data from database d1 to database d2. But i still want to apply transnational logs from database d1(in Server A) to database d2.Is that possible?

This might seem a generic/broad question?But it is a valid question as i have similar scenario of migrating legacy data and current production data into a new sql server after merging current production into legacy, and stay in synch with current production.

I thought of log shipping and tried as well.The issue was server A is in lower version(2008 R2) and server B in higher version(2016).So i have to run log shipping in no recovery mode and i can't do merging on the data in secondary in no recovery mode.If i stop log shipping and bring the database to recover mode and do merge ,i can't put it back to non recovery mode again and enable log shipping.

Please note i merge database d1 to d2 in server B.

  • Have you looked at Log Shipping? – Erik Darling Jun 20 '18 at 13:59
  • I thought of log shipping and tried as well.The issue was server A is in lower version(2008 R2) and server B in higher version(2016).So i have to run log shipping in no recovery mode and i can't do merging on the data in secondary in no recovery mode.If i stop log shipping and bring the database to recover mode and do merge ,i can't put it back to non recovery mode again and enable log shipping. – user9516827 Jun 20 '18 at 14:07
3

No. In order for a log to be restored to a database, the log sequence number (LSN) of the log has to be the correct number to match what the database requires for the next log to be restored. If you could somehow get the LSNs to match, restoring a log from a different database would corrupt it as some operations contain page IDs, and the page IDs are not going to be the same on both databases.

There are probably quite a lot of other reasons why this won't work, but it really isn't necessary to document all the reasons why as it is something that is simply not designed or expected to be able to work that way.

1

As Tony said, to restore/synchronize log LSN of logs should match LSN of the restored database d2 (that yuouwant to synchronize) on server B.

Apart from that, to restore/synchronize transaction log(T-Log)/logs, Full backup must be restored with NORECOVERY mode. it keeps database in "Restoring" mode so that logs (T-Logs backup taken after Full/Differential backup) could be restored.

Once you restore database WITH RECOVERY, you can't match LSN of d1 on server A with d1 or d2 on server B.

First of all, you should decide purpose of synchronizing d1 on server A with d2 on Server B.

If you want to use d2 for Read-Only, Cold Stand-by or DR purpose you can use Log Shipping feature.

If you need NEED to execute DML as well, you can use Replication.

Thanks!

  • Purpose: d1 in server A is the current production,so i have bring all the updates on it,while i merge legacy data.Since server A is in lower version and server B in higher version,when i do log shipping the database stays in non recovery mode. – user9516827 Jun 20 '18 at 15:02
  • Then it's better to go with Transnational Replication – Rajesh Ranjan Jun 20 '18 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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