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.

:)

Improve this question
2
  • Is it possible for you to configure Transaction Log shipping between source and destination database ?
    – Shanky
    Commented Dec 17, 2014 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
    Commented Dec 17, 2014 at 18:42

1 Answer 1

Reset to default
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.

Improve this answer
4
  • By saying "If so, you can restore the database backup and leave it in RECOVERY mode." do you mean NORECOVERY mode?
    – Nanami
    Commented Dec 17, 2014 at 18:27
  • Yes, sorry about the error. I have corrected the mode to NORECOVERY.
    – RLF
    Commented Dec 17, 2014 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
    Commented Dec 17, 2014 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
    Commented Dec 17, 2014 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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