Questions about SQL backups and Tlogs

[ajohns]

We have Veaam B&R v10 and about to upgrade to v11. We have Veeam doing VMWare image-based backups of our SQL servers with application aware enabled with copy-only for transaction logs. I did a DR test with one of our SQL DBAs and they brought up a few questions that I was hoping someone here could help answer for us.

We want to be sure we are using the best option here in Veeam that is for reliability and performance but also point in time for SQL. We work with big indexing jobs and this could be a problem. My understanding is that native SQL backups don't affect the backup chain whereas Veeam does. Is this still true? I recall this was a problem back in v9.

• Should we be doing periodic backups with application aware?

• Or should we stay with copy-only or turn off truncate so that we can do differential backups all the way through the chain backup?

• I want to be able to just grab a SQL database and restore it. How can we do that quickly without say having to spin up an entire VM?

Thanks
ajohns

[PetrM]

Hi Aaron and Welcome to Veeam R&D Forums!

Answering your questions:
1. "Should we be doing periodic backups with application aware?"
Yes, you should use application-aware image processing to produce transactionally consistent backups of SQL workloads.

2. "Or should we stay with copy-only or turn off truncate so that we can do differential backups all the way through the chain backup?"
You should stay with copy-only. Copy-only processing is still part of application-aware image processing. Veeam will use VSS_BS_COPY flag for VSS snapshot creation, thus we can prevent LSN update and preserve backup history of the chain that is handled by native/3rd-party tools. I suggest to review this page of our help center for more details.

3. "I want to be able to just grab a SQL database and restore it. How can we do that quickly without say having to spin up an entire VM?"
The best option is to use Veeam Explorer for Microsoft SQL. This tool has a lot of nice features like Instant Recovery, Publish, Export etc.

Thanks!

[ajohns]

Hi PetrM,

Thank you for the quick response. I'm going to keep the Veeam SQL backups on with application aware copy-only. As far my SQL DBAs, will they still need to continue using the native SQL Maintenance Plan on top of the Veeam SQL backups? Or is that unnecessary? My thoughts are that if they need point in time restores that they should continue to do the SQL Maintenance Plan on top of our Veeam SQL backups.

Thanks
ajohns

[PetrM]

Hello,

Basically, it's up to DBA to decide either they want to continue native backups of SQL or to rely just on Veeam image-level backups. I believe that this decision depends on requirements for restore. If the point-in-time restore is the only requirement, it would be enough to enable T-log processing in backup job instead of copy-only. In this case, it will be possible to perform point-in-time restore using Veeam Explorer for Microsoft SQL. Otherwise, only restore to the point in time of the selected image-level backup will be available in this step of restore wizard.

Thanks!