Availability for the Always-On Enterprise
Post Reply
bmink
Lurker
Posts: 1
Liked: never
Joined: Mar 01, 2016 3:40 pm
Full Name: Brandon Mink
Contact:

SQL Backed Up Servers Query

Post by bmink » Mar 02, 2016 2:40 pm

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
Veeam Software
Posts: 4247
Liked: 349 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: SQL Backed Up Servers Query

Post by PTide » Mar 02, 2016 3:34 pm 1 person likes this post

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
Veeam Software
Posts: 16691
Liked: 1343 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Backed Up Servers Query

Post by foggy » Mar 02, 2016 3:39 pm 3 people like this post

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.
Veeam Software
Posts: 21398
Liked: 1273 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backed Up Servers Query

Post by Vitaliy S. » Mar 02, 2016 3:48 pm 1 person likes this post

bmink wrote: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
Expert
Posts: 138
Liked: 10 times
Joined: Jul 17, 2015 9:02 am
Full Name: Glenn L
Contact:

Re: SQL Backed Up Servers Query

Post by btmaus » Jan 25, 2017 11:30 am

foggy wrote: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

v.Eremin
Veeam Software
Posts: 15077
Liked: 1133 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Backed Up Servers Query

Post by v.Eremin » Jan 25, 2017 11:44 am

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
Expert
Posts: 138
Liked: 10 times
Joined: Jul 17, 2015 9:02 am
Full Name: Glenn L
Contact:

Re: SQL Backed Up Servers Query

Post by btmaus » Jan 25, 2017 12:07 pm

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.

v.Eremin
Veeam Software
Posts: 15077
Liked: 1133 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Backed Up Servers Query

Post by v.Eremin » Jan 25, 2017 1:59 pm

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
Expert
Posts: 138
Liked: 10 times
Joined: Jul 17, 2015 9:02 am
Full Name: Glenn L
Contact:

Re: SQL Backed Up Servers Query

Post by btmaus » Jan 25, 2017 9:23 pm

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

v.Eremin
Veeam Software
Posts: 15077
Liked: 1133 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Backed Up Servers Query

Post by v.Eremin » Jan 26, 2017 9:02 am

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
Expert
Posts: 138
Liked: 10 times
Joined: Jul 17, 2015 9:02 am
Full Name: Glenn L
Contact:

Re: SQL Backed Up Servers Query

Post by btmaus » Jan 26, 2017 9:29 pm

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?

v.Eremin
Veeam Software
Posts: 15077
Liked: 1133 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Backed Up Servers Query

Post by v.Eremin » Jan 27, 2017 2:43 pm

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.

Post Reply

Who is online

Users browsing this forum: No registered users and 41 guests