Hybrid strategies for SQL Server

[Someonenew2014]

Hi...

Just quick question.
We configured Veeam to backup MSSQL along Tlog every 60 minutes. As a secondary level of backups DBA would take another copy using Full and differential backup without Tlog backup.

We read the documentation but found we should not take with COPY-ONLY where DBA confused. Please advice
how DBA can take another level of backup without compromising Veeam Chain backup.

Thank you.

[Vitaliy S.]

Hi,

Can you please point me to the documentation where it says you cannot use copy-only mode? Using copy only (in the SQL Management Studio) should be exactly what you want to do for maintaining two backup plans for the SQL Server. This will allow you to do full backups via 3rd party tool without breaking Veeam backup chain.

Thanks!

[Someonenew2014]

Thanks.

The paper is here

https://www.veeam.com/wp-sql-server-bac ... veeam.html

page 22

=================================here====================================
This strategy means you will let Veeam produce its snapshot (restore point) and also transaction log

backups. The question now is whether we can perform this while also performing SQL Server backups

to file – and not have SQL Server somehow interfere with the Veeam snapshot and log backups?

The answer is simple... Yes, we can!

You can produce full backups any way you want – they won’t interfere with the Veeam snapshot or

log backups. But you don’t want to use the COPY_ONLY option for these full backups, if you also want

differential backups (see next paragraph).

If you want, you can now complement these full backups with differential backups. For instance, you

perform a full backup every night and a differential backup every four hours. Full backups or differential backup do not interfere in any way with the chain of log backups produced

by Veeam.

=====================END=========================================

I cannot understand something wrong here or not clear ?

[Vitaliy S.]

Thanks for the reference. You definitely can do full backups of your databases using COPY_ONLY method, but keep in mind that every backup job run will require new full backup in the SQL Management Studio (if you want to use differential mode also).

You can follow either of the options:

1. Do full and differential backups of your SQL database, but in this case you will need to initiate full backup after Veeam backup job run, otherwise your differential backups will be unusable.
2. Do full backups only using COPY_ONLY method. In this case you don't need to do micromanagement when running Veeam backup jobs.

If I were you, I would go with option 2. Hope this helps!

[Someonenew2014]

If I were go with option 2.

Using COPY_ONLY for both full and differential will not affect Veeam chain?

In my example take weekly full and daily differential via SQL management studio and full via Veeam along with Tlog backup. Sounds good?

============================================
Please ignore. Diffrenial backup cannot be used with COPY_ONLY.

So only full and Tlog backup can be used in conjunction with COPY_ONLY

[Vitaliy S.]

Yes, that's correct, COPY_ONLY cannot be used for differential backups.