Small transaction logs and/or transations logs with no transactions and need to restore

  • We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. We are taking a full backup on Sunday night, a diff backup Monday through Saturday night, and trans log backups Monday through Friday every 10 minutes from 7 A.M. to 6 P.M. We only have a test database set up for right now. I noticed that the TLog Backup for 7 A.M. is 15,524 KB and the TLog Backups from 7:10 to 6 P.M. are only 99 KB each. I understand the TLog backups from 7:10 A.M. to 6 P.M. are small and the same size because of little or no activity on the database. My question is, if there is no (zero) updates on the database, would there be any transactions in the TLog Backup? If there are no transactions in the TLog Backup is it still required for it to be restored in sequence during a recovery (after the full backup restore and diff backup restore)?

    Thanks in advance, Kevin

  • Since you should always test your backups (so that you know that they work, and that you know that you are familiar with the activity), why don't you try and see what happens?

    (I am quite sure that the answer is that you need to restore all these log backups.)

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • You need to restore ALL log backups, in sequence.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/5/2013)


    You need to restore ALL log backups, in sequence.

    +1

  • Thanks for the responses. Steve what do you mean by +1?

  • kevinsql7 (2013-08-06)


    Steve what do you mean by +1?

    That he agrees with Gail.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply