BE2012 SQL Full backup without affecting other Incrementals

[SiT]

Hi,

I am currently setting up BE2012 to backup our SQL 2008 cluster.

The Full backup each week and subsequent incremental backups seem to work fine to our tape library but we also need a daily full overwrite backup to disk so we can move to an offsite storage.

The problem i would have (i think) is will the incremental backups then compare to the daily full or the weekly full?  Ideally i want them to compare to the weekly as those backups will be in the same place and we won't have to restore the offsite backup to restore the incremental for it.

One of the other issues that i forsee is that when i was initially setting the backup up, the incrementals kept failing due to SQL itself running its own backups and the incrementals knew that BE did not do the last full backup.  The SQL backups had to be stopped for this to complete without errors.

Not sure if i have made this more confusing than it actually is...

Any help appreciated.

Thanks

[Colin_Weaver]

Why don't you do a daily full to disk that stays connected to the server with a duplicate job of that full to either tape or a removable disk for the offsite storage.

Then you incremental sets can remain in local storage referencing the local full with no issue

Also with your second issue as you have already discovered if you run a SQL backup with any other product then for BE to run an Incremental SQL backup - you must run another full backup first.

[SiT]

Hi Colin,

The daily full to tape would negate the need for the daily incrementals surely?  The local storage is not that large and is just enough for one full overwite (which is then copied offsite).  Is there not a way to get the incrementals to refer to the full that was done on the tape library and not the disk?

Thanks

[SiT]

"Backup 00026" backs up to local storage, daily overwrite (short term for upload to offsite).

"All Tables" backs up to local tape library.

"Backup 00026" would run every day, would the full backup "Backup 00026" effect the differential backup in "All Tables" which runs mon-thu and it has its own full backup on Friday?

i.e. would the differential backups in "All Tables" backup changes since the full backup in "Backup 00026" or the full backup in "All Tables"?

Hope this makes it more clear...

[pkh]

would the differential backups in "All Tables" backup changes since the full backup in "Backup 00026" or the full backup in "All Tables"?

It depends on which is which full backup is run later.  The differential will use the last full backup as the baseline.  If "Backup 00026" runs after the full backup in "All Tables", then "Backup 00026" will be the baseline for any subsequent differential backups.

[SiT]

Ok.

I have found that i can add a stage to the "All tables" job so that it duplicates the Full end of week backup to disk as soon as that job finishes.  I will have to take the hit on how many times i upload i.e. weekly instead of daily.  Would this then be the reference full backup for Differentials?

Thanks for your help

[pkh]

If you run "Backup 00026" after the full backup in "All Tables", then "Backup 00026" will be the baseline for any subsequent differential backups.

[SiT]

I understand that bit..

But if i do the duplicate to disk on "All Tables" (totally ignoring the "Backup 00026"), will the duplicate class as a reference full backup or will it still be the tape?

[pkh]

Whether you duplicate the backup or not does not matter, it is when the full backup is taken that matters.  The media does not matter.

[SiT]

So the duplicate to disk that occurs AFTER the backup will not be required by a differential restore as this is not classed as a backup?

[pkh · Accepted solution]

You can use either the original full backup set or the duplicated full backup set when you do a restore.  They are identical so it does not matter which one is used.  However, the restore process in BE 2012 is smart and it will use the backup chain which means that the original backup set will be used in the restore.