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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jose TorresCertified Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.