Keeping database in synch

Question

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.

Answer 1

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.

Answer 2

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!