The affect on differential backups when Protecting SQL DB with multiple Backups products at the same time

I am wondering what happens to the SQL full backup and differential backup relationship in one backup product if another backup product is also doing full backups on a daily basis.

We are using one backup solution to protect the sql server locally on a daily basis and this solution performs a full SQL backup.  We also have a second backup solution that backups up the SQL DB offsite to the cloud and it performs a full backup once a week and a differential backup everyday.  What happens to the Full backup Differential backup relationship of the cloud solution if another solution is creating full backups.  Which system keeps track of the backup LSN numbers, is it SQL or the backup solutions?

For example if I try to do a restore of the cloud backup solution will the differential backup restore work or will it be linked to the on premise full backup as apposed to the cloud full backup?
lurcoAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Unless you specify otherwise, the last full backup will become the base for all diffs from that point forward.  for example:

FullBackup1 then DiffBackup1; DiffBackup2; FullBackup2; DiffBackup3

DiffBackup3 will only work on FullBackup2, NOT on FullBackup1.

Put more simply, perhaps, diffs apply only to the last full backup created before that diff.

When you do a backup, you can explicitly "tell" SQL not to use the backup you are about to make as a base to do differential restores.  You do this by specifying "COPY_ONLY" on the full backup.  A backup with "COPY_ONLY" specified will not reset the "diff markers" and thus will not affect diffs at all, and will not allow a diff to be applied to it after being restored (afaik; I don't see how one could be).
0
 
Jose TorresSenior SQL Server DBACommented:
The first thing about deciding your backup strategy is to first decide on your recovery strategy.
So the first question to answer is from where do i get my backups during a recovery.
Answering this question will determine which ones should be copy_only backups.

Also in your description you only defined full and diff backups, this leads me to believe that  the database is in simple recovery.
If you are running full recovery model then you should and this is a production environment you should seriously think about performing log backups.

In most production environments generally you will keep 1 or more of the backup files on local storage (depending on your size restrictions) but always you send them off server and then off-site.
0
 
lurcoAuthor Commented:
Thank for your help, its a lot clearer now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.