Home / server / Questions / 194047
In Process
tjinak
Asked: 2010-10-23 18:31:29 +0800 CST

SQL Transaction log backups after Full SQL Backups

  • 772

Environment:

  • VM with 20 gb of HD space and secondary hard drive with 3 gb.
  • Windows Server 2003 and Sql 2005

Backup Scenario:

  • Weekly Full backups with Backupexec
  • Nightly Diff. backups with Backupexec
  • Daily 15 min. log file backups maintenance plan

Question:

I'm backing up my 15 min log file backups to the secondary 3gb hard drive. What do I do with them after the Nightly Differential backup? Right now my log file backup is adding onto itself, do I just move it to a different server right after the differential with a scheduled task? Will the maintenance plan be able to just start right back up after I move the no longer applicable log file backup?

I've read a lot of the posts on here, sql books online, sql fansites, even YouTube and this seems to be the easiest approach save for this last piece of the puzzle. I'm testing it on this little server before I implement this on my bigger servers.

sql-server-2005 backup disaster-recovery transaction-log

3 Answers

  1. With the good differential backup after the full backup, you can discard the tran log backups between the full and the differential backups. So from a recovery perspective, you would do the full, the differential, and all the tran logs after the differential.

    For a really clear explanation, see Paul Randal's TechNet article on understanding SQL Server backups. Scroll down to Figure 4 and start at the paragraph right above it. It gives the exact scenario you're talking about.

    • 5

  2. Why not use Backup Exec for your transaction log backups? It seems to me that by using two separate methods of backup for SQL, you're making things more complicated for yourself.

    In Backup Exec, if you have a backup-to-disk folder, you could create a media set for transaction logs, which have a 24 hour Overwrite Protection Period. You could then use a Backup Exec policy to manage your weekly fulls, daily differentials, and 15-minutely transaction log backups.

    Backup Exec would manage your transacation log backup files, overwriting old ones as they fall out of the overwrite protection period.

    Then, if you need to restore, all your restores are taking place from one place, using one tool.

    • 1

  3. These questions may seem rudimentary, but you didn't specifiy in your question and I don't want to assume the answers so...

    Is the nightly BackupExec differential backup backing up SQL Server? If so, what type of SQL backup is occuring during the nightly differential? Is it a differential SQL backup via BackupExec?

    What's the purpose of the 15 minute transaction log backups? Is that your data recovery mandate? What would happen if you lost 30 minutes of data, 2 hours of data, etc? What would happen if you had to restore back to the nightly differential or the weekly full backup?

    How long do you need to retaing the backups? Can you discard older backups? If so, what's the aging cycle for discarding older backups? One day, one week, etc?

    • 0