SQL Server – What Happens When Restoring the Same Transaction Log Multiple Times?

restoresql servertransaction-log

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

Best Answer

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

Related Question