Home Forums SQL Server 2005 Backups Log backups while Full/Differential backups are running RE: Log backups while Full/Differential backups are running

  • GilaMonster (4/21/2013)


    Let's see... what happens when we have a 6-hour full backup duration and a 15 minute data loss SLA and log backups don't run during that full backup?

    Smart-assery aside, I think it's still a legitimate question. What state are such backups in when they are taken? What does a transaction log backup give you when it's taken during a full or differential backup? Where do you start and what do you use if you need to restore such a database? It never occurred to me, since I have such small datasets (cca. 100MB) that it's not an issue - even full backups run in less than a minute, and I have them scheduled for late at night, when the server and database are completely idle. I have it simple - nightly full, hourly diff and quarter-hourly trans, the last two only during working hours, with checks in the code to see if the database changed during the preceding appropriate interval. No possibility of overlap, so restore (and yes, I try it regularly to be sure it works) is a simple matter of stepping up to the point of failure. But I have no idea how I would deal with overlapping schedules.

    I have Shawn McGehee's SQL Server Backup and Restore book, but I don't recall that issue being addressed in it. I wasn't specifically looking for that when I read it, though, so I may have missed it.