1

we have an external production database (maintained by another company) and we are trying to build an internal database that replicates the production database for reporting purposes (both sql-server).

Here is the scenario: they have sent us a full backup (BAK) which I already have it restored properly. And they will send us the transaction logs (TRL) on a daily basis.

I know I can restore it with BAK plus all the TRL sequentially and overwrite the previous restore. But is there a way that I can restore with the most recent log I received 'incrementally'?

Thanks in advance. And correct me if I used inappropriate terminologies.

:)

  • Is it possible for you to configure Transaction Log shipping between source and destination database ? – Shanky Dec 17 '14 at 18:29
  • No. The source database is maintained by another company and it would take a lot of conversations to get anything changed. @Shanky – Nanami Dec 17 '14 at 18:42
0

It depends on whether your local server is Enterprise Edition.

If so, you can restore the database backup and leave it in NORECOVERY mode. You will not be able to directly access the database, but you can create a SNAPSHOT database which will be Read Only.

That snapshot database can be dropped, the new TRL restored in NORECOVERY Mode, and recreate the snapshot.

  • By saying "If so, you can restore the database backup and leave it in RECOVERY mode." do you mean NORECOVERY mode? – Nanami Dec 17 '14 at 18:27
  • Yes, sorry about the error. I have corrected the mode to NORECOVERY. – RLF Dec 17 '14 at 18:40
  • Thank you for the suggestion. Since this replication is only for reporting purposes, I think read-only mode is good enough as long as other middleware works fine. I also heard there is a standby mode which is in norecovery mode and provides read-only access at the same time, I will try that too. Thanks again! – Nanami Dec 17 '14 at 18:49
  • Yes,RESTORE WITH STANDBY is an option. The documents say "for limited Read Only use" but it may work for you. I have not tried extended use. – RLF Dec 17 '14 at 18:54

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.