2

I’m currently trying to keep a copy of our production database up to date by restoring a full backup, followed by a differential, then supplementing with transaction logs as they become available from the production database. The source is SQL Server 2008 Enterprise Edition and the destination is SQL Server 2014 Web. Doing this I noticed that you can restore the same log multiple times (as long as a newer one hasn't been restored after).

Question: what happens when you restore the same transaction log multiple times? Does it get overwritten? Discarded? Is it screwing up my database?

E.g.

sqlcmd -E -Q "RESTORE LOG [databasename] FROM DISK = 'D:\ LOG\SERVERNAME_databasename_LOG_20160809_141504.trn' WITH NORECOVERY" Processed 0 pages for database 'databasename', file 'databasename_dat' on file 1. Processed 0 pages for database 'databasename', file 'MyDBDat' on file 1. Processed 65662 pages for database 'databasename', file 'databasename_log' on file 1. RESTORE LOG successfully processed 65662 pages in 27.589 seconds (18.593 MB/sec).

sqlcmd -E -Q "RESTORE LOG [databasename] FROM DISK = 'D:\LOG\SERVERNAME_databasename_LOG_20160809_141504.trn' WITH NORECOVERY" Processed 0 pages for database 'databasename', file 'databasename_dat' on file 1. Processed 0 pages for database 'databasename', file 'MyDBDat' on file 1. Processed 65662 pages for database 'databasename', file 'databasename_log' on file 1. RESTORE LOG successfully processed 65662 pages in 26.238 seconds (19.551 MB/sec).

Improve this question

1 Answer 1

Reset to default
1

Restoring the same transaction log over and over buys you nothing. The changes in the log will have already been applied to the database files after the first restore and no further action is needed on subsequent restores of the same transaction log backup.

Also, keep in mind that you cannot access a database until you have issued the with recovery command UNLESS you are using a standby file which will enable read only access.

Depending on WHY you are wanting to keep in sync with your production database, it sounds like you might want to research log shipping

Improve this answer
2
  • Thanks. I am not really trying to accomplish something by restoring the same log file again and again, I just noticed that it was possible by accident and wonder if it has any negatives (so that I need to keep closer track of what log files I have restored already). The two servers can't communicate directly, which I believe is needed for log shipping.
    – Brian Lund Larsen
    Commented Aug 9, 2016 at 15:13
  • I 'think' you just need a common 'share' that they both can reach. The primary would write logs to the 'share' and the secondary would read from the 'share'. One thing that is worrisome is you are using Enterprise backups and restoring to non-Enterprise servers. That would be a problem if someone decides to start using 'Enterprise-Only' functionality (like data compression) in the source database because the target won't support it.
    – Scott Hodgin - Retired
    Commented Aug 9, 2016 at 15:32

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.