full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

sql