SQL Backed Up Servers Query

[bmink]

Hello all -
We have been in the process of implementing Veeam into our environment. The implementation is currently being handled by our Systems Operations department so as a DBA we are a bit stuck on the easiest way to determine on a daily or weekly basis which SQL Servers have been moved onto Veeam for backups. I was curious if anyone had a query out there that they would use to query the Veeam Databases to find out any SQL Servers that are currently being backed up. Does Veeam happen to place a flag in the database to show that a server is a SQL Server?

- Thank you for your help

[PTide]

Hi and welcome to the community!

In order to perform a transaction consistent backup of an SQL server an Application Aware processing should be used. When you use application-aware processing VSS writers are leveraged so normally if an SQL server is being backed up you should be able to trace SQL Writer Service activity. Another approach to perform a transaction-consistent backup is to trigger SQL native tools via pre-freeze scripts. In this case you can track backup-related activity in SQL server logs.

Thank you.

[foggy]

There are also a couple of Veeam ONE reports that might be useful:

- if you're backing up SQL Server logs, you can review the SQL Backup Job Historical Information report to see all such VMs;
- there's also a general Protected VMs report.

[Vitaliy S.]

Does Veeam happen to place a flag in the database to show that a server is a SQL Server?

There is no flag for applications in the VM, however you can categorize/create groups for all SQL Server VMs via Veeam ONE and then scope the Protected VMs report to this group. This will give you the information about available restore points for your SQL Server VMs.

[btmaus]

There are also a couple of Veeam ONE reports that might be useful:

- if you're backing up SQL Server logs, you can review the SQL Backup Job Historical Information report to see all such VMs;

I'm trying to use this report for my DBA team, however the information is blank in the report.

Any idea's?

Support case ## 02046081

[veremin]

Might be silly questions, but are you positive that

- Backup server is added to Veeam ONE and information from it has been already collected successfully?
- Application Aware Image Processing as well as SQL transaction log backup job is enabled for those VMs?

Thanks.

[btmaus]

Hi v.Eremin,

Thanks for replying.

Yes the backup server has been added to Veeam ONE from day 1. All the other reports work OK.

As for the backup jobs, I have 9 jobs, one for each SQL server. I do have AAIP enabled for all of them. On the General tab of the AAIP section, I have them all set to require succesfull processing and to process transcation logs. On SQL tab of AAIP, I have them all set to truncate logs. The databases are all a mix of either Full or Simple mode.

[veremin]

So, you're just truncating logs instead of backing up them periodically (third option on "SQL" tab)? The corresponding report is supposed to show the results of such jobs (SQL logs periodic backups), therefore, it shows nothing in your case. Thanks.

[btmaus]

Will the report only show those databases that are in Full mode then?

[veremin]

If you want to perform DB point-in-time restore, you need to switch DB to Full recovery mode and backup its logs periodically, using either native tools or VB&R (SQL log backup job).

DB in Full recovery mode which logs are backed up via VB&R SQL log backup job will be shown in the said report.

Thanks.

[btmaus]

I don't want to perform point-in-time restores. We backup once a day, and clear the logs of certain databases that are in Full mode, and others are in Simple mode. This works for us.

Disappointed that we cannot get a report from Veeam ONE that shows us just the SQL jobs. Can this be a feature request?

[veremin]

This report has been designed with different use case in mind - monitor state of regular SQL log backup jobs.

In your case, you can check the Protected VMs report and see whether SQL VM is shown there, since there is nothing specific regarding this VM which would require additional report.

Thanks.