Post reply

Full DB Backup Restore and Transaction log backup Restore

  • August 1, 2012 at 7:25 am

    #260058

    Hi -

    (SQL 2008 R2)

    For some reason, I was under the impression once a full database backup completes successfully and restored onto another SQL instance (with NO RECOVERY), previous transaction log backups can not be restored.

    So, i performed a full DB backup at 9:00 (on sql instance A), restored the full backup onto sql instance B leaving the database with no recovery (able to apply transaction log backups). Then at 9:05, performed a tlog backup on A and restored it to B, again leaving the database with no recovery.

    Then at 9:10 - I performed another full backup on A...

    At 9:15, I performed a transaction log backup on A, and restored it to B successfully. Then restored the database on B with recovery.

    ALL the changes i made between the second full from 9:10 and the transaction log backup at 9:15 were present on the B database.

    so - as long as I don't change the recovery model - I can continue to take daily full backups (on instance A) and continue to apply hourly transaction log backups to the secondary instance (B)?

    Any information you can provide is appreciated.

  • August 1, 2012 at 7:48 am

    #1520277

    Full backups do not truncate the transaction log.

    Not sure why you would want to take hourly full backups...

    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

  • August 1, 2012 at 8:07 am

    #1520291

    Hi -

    Apologies for being unclear.

    We perform hourly transaction log backups. And full backups daily.

    So - if a full backup is taken at 9:00 can the 10:00 hourly transaction log backups be applied to a database restore (with norecovery) originally restored from the previous days full backup?

  • August 1, 2012 at 8:15 am

    #1520297

    Providing that all the log backups since yesterday's backup have already been restored, yes. As I said, full backups do not truncate the transaction log.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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