SQL Backed Up Servers Query

Availability for the Always-On Enterprise

SQL Backed Up Servers Query

Veeam Logoby bmink » Wed 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
bmink
Lurker
 
Posts: 1
Liked: never
Joined: Tue Mar 01, 2016 3:40 pm
Full Name: Brandon Mink

Re: SQL Backed Up Servers Query

Veeam Logoby PTide » Wed 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.
PTide
Veeam Software
 
Posts: 2894
Liked: 236 times
Joined: Tue May 19, 2015 1:46 pm

Re: SQL Backed Up Servers Query

Veeam Logoby foggy » Wed 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.
foggy
Veeam Software
 
Posts: 14482
Liked: 1057 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: SQL Backed Up Servers Query

Veeam Logoby Vitaliy S. » Wed 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.
Vitaliy S.
Veeam Software
 
Posts: 19405
Liked: 1089 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: SQL Backed Up Servers Query

Veeam Logoby btmaus » Wed 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
btmaus
Expert
 
Posts: 123
Liked: 8 times
Joined: Fri Jul 17, 2015 9:02 am
Full Name: Glenn L

Re: SQL Backed Up Servers Query

Veeam Logoby v.Eremin » Wed 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.
v.Eremin
Veeam Software
 
Posts: 12893
Liked: 939 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: SQL Backed Up Servers Query

Veeam Logoby btmaus » Wed 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.
btmaus
Expert
 
Posts: 123
Liked: 8 times
Joined: Fri Jul 17, 2015 9:02 am
Full Name: Glenn L

Re: SQL Backed Up Servers Query

Veeam Logoby v.Eremin » Wed 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.
v.Eremin
Veeam Software
 
Posts: 12893
Liked: 939 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: SQL Backed Up Servers Query

Veeam Logoby btmaus » Wed Jan 25, 2017 9:23 pm

Will the report only show those databases that are in Full mode then?
btmaus
Expert
 
Posts: 123
Liked: 8 times
Joined: Fri Jul 17, 2015 9:02 am
Full Name: Glenn L

Re: SQL Backed Up Servers Query

Veeam Logoby v.Eremin » Thu 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.
v.Eremin
Veeam Software
 
Posts: 12893
Liked: 939 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: SQL Backed Up Servers Query

Veeam Logoby btmaus » Thu 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?
btmaus
Expert
 
Posts: 123
Liked: 8 times
Joined: Fri Jul 17, 2015 9:02 am
Full Name: Glenn L

Re: SQL Backed Up Servers Query

Veeam Logoby v.Eremin » Fri 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.
v.Eremin
Veeam Software
 
Posts: 12893
Liked: 939 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: antasik, baber and 16 guests