How to make a hourly backup policy for transaction log backup in Netbackup 6.5

[dakillacat]

I'm a newbie to Netbackup 6.5 and need to create hourly backups for MS-SQL transaction logs. I created a policy with a weekly full, daily differential incremental and an hourly differential incremental schedule. I need the hourly schedule to start backups 15 minutes after the top of the hour, every hour. I can't find a way to do that. I searched the forums and did not find any answers that were detailed enough on how their hourly transaction logs were configured.

I'm not sure what I should put in the start window either. I made it to start at 12:20 am on Sunday and end at 12:15 am Sunday for a duration of 6 days 23 hours, 50 minutes. Is this right or do I just set a start time of 12:20 am on Sunday, and end time at 12:30 am on Sunday and leave the rest blank? If I do that will the hourly frequency start jobs 20 minutes after the hour forever? Any help is appreciated. Thanks!!!

[Ver-Cap]

The frequency mentioned for the time interval will ensure the backup to trigger, may be u can select the start time to 15 minutes after the top of the hour so as per the schedule the backup will trigger in the time interval...

Best Regards,

Kitty

[sdo]

You could try...

Make your "Application Backup" schedule 24x7.

For the "Automatic Backup" schedule, which triggers the application backup, change the resolution to 5 mins, set a schedule/run window from 00:15 to 23:30, set to frquency based (not calendar based) and set a frequency of 1 hour.

...but your real problem here is schedule creep... i.e. assume your master is slightly busy and the very first occurence queues a backup at 00:15:01 (i.e. it takes one second the master to create the backup job) when the next occurence becomes due the master is again busy, and because the frequency is 1 hour the master can't queue until 01:15:01, but actually queues at 01:15:02, which means the next queues at 02:15:03... and so on... and for the schedule setup above this will continue ad nauseum.

But, you stipulate that your backups must run on the quarter hour, and I assume there's a good reason for this, so...

The only thing I can some up with is 24 "Automatic Backup" schedules...

Schedule_00   queues at 00:15 every day, with a one hour window, and a frequency of two hours.

Schedule_01   queues at 01:15 every day, with a one hour window, and a frequency of two hours...

...

Schedule_23   queues at 23:15 every day,   "     "     "     "    "  ....

HTH.

[Dion]

The other option is to schedule the job externally from NetBackup.  Here you will create one TL job with a schedule that does not have a open window on the Automatic Backup and then, using cron/windows scheduler or some other scheduler, schedule the jobs for every 15min using the command-line option, bpbackup.exe.

I used to use this option quite a lot when backups were based on progress of the evening batch run rather that time.  It worked like a treat.

[sdo]

Dion, nice idea :)

Remember to use the "-i" switch on bpbackup to run a normal schedule name.  N.B. "user mode" schedules are not allowed in MS SQL policy types, so client side is not usually able to schedule/run the application backup, i.e. Dion's suggestion normally has to be scheduled on a master or a "server" of the master (sometimes, in looser security sites the media servers are full "servers" of the master)...

...however, it is possible to allow the client to execute bpbackup with the "-i" switch, but to do this you have to add the client name to the server list of the master (i.e. grant the client full control over the master) - which, depending on security model, might be out of the question.  But this would allow your DBAs to control the scheduling of the backups, and not have to request the NetBackup admin to de-activate/re-activate the policy when DB maintenance tasks are underway.

[dakillacat]

Kitty,

Thanks for your reply. I changed the time to start at 12:15am Sunday and stop at 12:00 am Sunday. I initialized the backup and it is still running after 1 hour with 70GB written so far. Will it first complete a full backup and then the do the hourly cumulative differentials?

Message Edited by dakillacat on 02-04-2009 02:31 PM

[dakillacat]

sdw303,

When you mention "Application Backup" is this a different product which is additonal to NetBackup. I did a search on that in the Help Pages of NetBackup 6.5 and it is a backup type that applies to all database agent clients and for more information on how to configure schedules for this type of backup, see the NetBackup guide that came with the product. These transaction logs have already been generated by MSSQL Server and can now just be treated as files that need backup and probably won't require another application.

The schedule creep is something that I did not think about. I will have to see if it experiences contention. I need to run every quarter hour because the files are generated at the top of the hour and the bigger log I've seen generated is 10GB and took about 10 minutes to make. I decided to use 15 minutes to have enough time to copy the newest changes to that directory.

I really don't want to make 24 backup schedules but will check out how others have done this type of backup. Thanks for your comments.

[dakillacat]

Dion,

Thanks for your input. So using your method will the scheduled bpbackup be initialized from the client. I suppose I will need to create a policy that will enable client initiated backups. Or will this be run from the NetBackup server? When you used this method and you needed to restore a backup is it just a matter of searching the media for the backup to restore?

[dakillacat]

sdw303

Thanks for answering my question from Dion's post. So I understand that the command must be issued from the server. I'm not using a MS-SQL policy so this method should be able to be run from the client right? I'm leaning more for setting it up from the NetBackup server to centralize where the backups are managed. If I do go this route how easy will it be to recover backups since they are all manual type backups? Is it just a matter of looking for backups based on date or policy type?

[sdo]

dakillacat - sincere apologies - I thought you would be using an "MS SQL" policy type, and thus only have two schedule types available "Application Backup" and "Automatic Backup" (these are the MS SQL policy schedule "types" ) .

Anyway, I see now that you will be using a normal file system backup, and I'm presume a Windows client (so your policy type will "Windows-NT" ) .  Again, you might think that the client could request the backups, but probably not in this case because "user mode" schedules are always full backups - and seeing as you want to do differential incremental to only pick up the latest trans logs - you can still go with one of:

1) One differential schedule with a big run window on each day and a frequency of one hour.

2) 24 differential schedules, each with a different run hour.

3) Dion's suggestion of a differential with an empty run window, and a cron job (or Windows scheduled task) to run a "bpbackup -i ..."

4) Client side cron/task scheduled job requesting the backup with "bpbackup -i ..." using Dion's suggestion, again a differential schedule type with an empty run window, but requiring you to grant the client "server" rights over the master.

For restores there's no difference to any normal restore, Dion's suggestion just put's the scheduling outside of "nbpem" (the policy execeution manager daemon/service).  Yes, restores will be normal "Windows-NT" and you'll be able to restore a "set" of transaction logs, just select the required date range in the restore GUI as you would with any normal full+cinc+diff combination (full + cummulative incremental + differential incremental).

Message Edited by sdw303 on 02-04-2009 11:29 PM

[dakillacat]

sdw303 - no worries...I finished running an initial fullback and had to do a manual hourly differential backup because I had an out of space issue

I do notice that the hourly differentials are not running every hour like I hoped. Is this because I made the run window to be 6 days:23 hours:45 minutes.

I will try choice 1 of your suggestions. You mentioned "a big run window on each day" I changed the differential schedule to start at 12:15am and end 12:00am every day with a frequency of 1 hour. This is how you suggested right. I will update if this solution works.

[sdo]

Sounds like a plan, but I still think you'll see the schedule creep.  Pls post back either way, I'd like to know if it's fixed or not.

[dakillacat]

It was not writing every hour so I had to make some changes. I enabled the checkpoint feature and set that to 60 minutes. I had to run it manually to start the differential backups. So far its working fine. It's backing up the transactions logs and I don't see any schedule creep so far. I will let it run over the weekend and report back. Thanks for everyone's input into this.