0

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.

tjinak
  • 31
  • 1
  • Hmm I'm wondering if the answer to this question is to change the Transaction log backup to "Overwrite" so when the Diff does get backed up it will overwrite the current one? – tjinak Oct 23 '10 at 02:37

3 Answers3

5

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.

K. Brian Kelley
  • 9,034
  • 32
  • 33
  • Thanks for the article, you're right that is my scenario. However, in Figure 4, it looks as though there are several log files and it appears that I'm not getting multiple transaction log backups during the day, only one? – tjinak Oct 25 '10 at 22:35
  • Use SQL Server Management Studio to look into the backup file where the transaction log backups are being written to. They are hopefully being written in append mode, meaning the file will contain multiple transaction log backups. It needs to contain all of them our you need to have separate files, so that the entire chain is maintained. – K. Brian Kelley Oct 25 '10 at 23:02
  • I switched it back to Append and it still didn't give me several files, however it did seem to grow much larger. I think we are probably going to go the route that hmallett said below and have backup exec do it. – tjinak Oct 27 '10 at 16:14
  • Even if you have Backup Exec do it, verify for sure that Backup Exec is doing it right with maintaining the tran log chain. I have seen cases where backup admins didn't understand what they were setting up and actually broke the backup chain, leading to a non-restorable situation. So check it, and then perform a test restore to a different system to verify. – K. Brian Kelley Oct 28 '10 at 18:10
1

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.

hmallett
  • 2,455
  • 14
  • 26
  • I'm not the person in charge of Backup Exec, so I'll have to talk to that admin. – tjinak Oct 25 '10 at 22:02
  • He's back and we looked and sure enough we found the setting to do it. Thanks for the input, this is probably the route we'll go.. just have it all centralized. – tjinak Oct 27 '10 at 16:15
0

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?

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • We have no policy for data recovery, that is part of this project. I'm not the BackupExec admin so I'll try my best to answer your questions. Before that admin went on vacation we spoke about a Grandfather, Father, Son setup? It is a differential SQL backup via BackupExec nightly. The purpose of the 15 min transaction log backup is to be able to restore to a point in time and to keep the log file as small as possible. Retainment is the BackupExec admin's responsibility so I'm not sure but thanks for mentioning it, it is something that should be added to the policy I'm working on. – tjinak Oct 25 '10 at 22:33
  • Thanks for the update. The reason I asked is because it sounds like you are defining your backup, recovery, and retention policies before defining the business requirements, which should come first. Why choose 15 minutes if 5 minutes or 60 minutes is required? – joeqwerty Oct 26 '10 at 00:19