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).

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

  • 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 Aug 9 '16 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 Aug 9 '16 at 15:32

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.