SQL Backup Job Historical Information

[ferrus]

I've just scheduled the SQL Backup Jon Historical Information report, for our DBAs.
The report worked, but the results were a bit unexpected. It only shows the minority of our SQL VMs, that use transaction log processing.
All of the SQL VMs on the list, that are in simple recovery mode, are omitted.

This is a shame, as it would be great to have a single report for all the SQL VMs - but it has me wondering if our SQL jobs are correctly configured.

At the moment, if a SQL VM doesn't require log processing, I leave Guest Processing Enabled, but set the Transaction Log processing to 'Perform Copy Only'.
Is it better to change this to 'Process transaction logs with this job' (even though there aren't any), and select 'Do not truncate logs' '?
That setting does mention that it requires Simple Recovery Model, but I've read conflicting posts that say they should just be set to Copy Only.

[ejenner]

The point about transaction logs is that they hold transactions which have not yet committed to the database. If you don't backup the transaction logs and just the database itself then you'll have an old copy of the database without the most recently added information (depending on your log settings).

If you have the standard database settings then transaction logs will have to be truncated every so often or else your log drive will run out of free space and too much of your database will be stored in log files. A backup program will often truncate the logs causing them to roll them into the database file.

When you'd be less likely to allow Veeam to truncate the logs is if you have another process happening on the database which also truncates the logs. No point in doing it twice. An example would be if you have Veeam backing up your SQL and you also use the built-in Microsoft SQL backup running alongside.

So if you're not truncating via another method then you should let Veeam do the truncation. You'll require rights within SQL for the account Veeam uses for the truncation and backup.

[ferrus]

I understand that - from the point of view of SQL DBs (whole VMs in our case) using the Full Recovery Model.

But for the tier below that - Simple Recovery Model DBs, that don't use transaction logs (or only one active log) - are we right in using the 'Perform Copy Only' for Guest Processing, or is the option labelled 'Do not truncate logs (requires simple recovery mode)' more appropriate.

To be honest, I'd never questioned our choice - until this report returned that we only had a third of our actual SQL servers.

[ejenner]

Even databases with circular logging enabled (a single log file) store information in the log file but commit to the database on a regular schedule so you keep one small (relatively speaking) log file.

So I'd probably still try to process the log files. But the best thing you can do here is to try restores to see what you can recover.

[ferrus]

Changing the settings to Process Transaction Logs, but not to Truncate them - didn't have any difference on the SQL Backup Job report.
It still only reports on the Full Recovery mode VMs.

Shame, it would be nice to have a single report for all SQL VMs - it's useful for the DBAs to monitor the jobs.