New SQL Server SureBackup script in Veeam B&R v8

Veeam Backup & Replication v8 has been generally available for almost a month now. It has a boatload of new features, and I recommend you to read the whole What’s new document since there are many “hidden gems” to be found.

SQL Server really got some love from v8 – Veeam Explorer for SQL Server, Transaction Logs backup, point-in-time restore… But there’s more! A new, enhanced consistency-checker script for SQL Server, to be used in SureBackup / SureReplica sessions!

DISCLAIMER: I’m certainly no DBA, so if you are, please don’t be too upset if you find something incorrect! 🙂 All feedback is welcome as usual, of course.

 

Background

Those of you familiar with Veeam B&R will know that in previous versions (from v5 up to v7), only a check on port 1433 (TCP) was enabled by default when you selected the “SQL Server” role in the Application Group wizard. This was a nice starting point, but chances are that you (or your DBA) want to be sure that the databases can be actually accessed.

Enter VB&R v8!

 

The SQL Server checker script

In VB&R v8, a new shiny VBS script is available: It is called Veeam.Backup.SqlChecker.vbs and you can find it in C:\Program Files\Veeam\Backup and Replication\Backup\

But what does it do?

– Connects to the specified database instance(s)
– Enumerates all databases (querying the “Master” database)
– Checks if those databases are available (with a simple USE command)

In the image below you can find a typical output of the script.

It supports both Windows and SQL Server authentication, comes with a detailed set of exit codes and can be configured to write logs to a location of choice. The code is also well commented, so just take a look inside and you’ll find pretty much all you might be searching for.

 

Exclusions (… it gets better!)

Now this is where the script gets really cool. Let’s say you have excluded some databases from backup, for whatever reason (the most common exclusion, which is automatic, is the vCenter database – “freezing” it during a backup makes the job fail). Well, the script lets you exclude databases or even whole instances! This ensures that your automated checks will not fail.

Unfortunately, there is a little typo in the instructions on how to define exclusions (Veeam R&D is already aware of this and a fixed script will be released in the upcoming patch #1 for VB&R v8). In rows, 60, 65 and 67 the second character should be a “g“, not an “a“.

EDIT [August 2015]: User Guy Schellens (in the comments below) reported another bug in the script and proposed a fix accordingly (thanks!)

You can download a fixed version of the file HERE if you wish.

NOTE: Instance and database names are case sensitive!

 

Authentication

Automated checks during SureBackup are run as VB&R’s service account, which by default in v8 is the local SYSTEM account. This of course makes it impractical to use Windows authentication to test SQL Server consistency. But the script also supports SQL Server authentication, so you can quite easily set up a test account in your SQL instances with minimum privileges and use these credentials when running the script.

As you can see, a “public” role/privilege is all that’s needed to successfully run the checks.

 

To use the newly created user with the SQL Server checker script you have to pass the username and password to it, as arguments. You have to configure your Application Group accordingly, customizing the script:

 

Bonus! Script: auto-create user/permissions

What if you have many databases/instances and want to create this test user automatically?

After a little research, I came up with an extremely simple SQL script that creates a login and maps it to all databases with minimum privileges. After playing with loops in SQL syntax (which I absolutely suck at) I discovered a nice, undocumented stored procedure: sp_MSforeachdb.
It cycles through all databases and executes a query/command on them.

Please note that this stored procedure is not supported by Microsoft and, under certain circumstances, it may not work correctly. See comments below for more information.

Just what I needed… This allowed my script to be 2 lines long!

CREATE LOGIN [veeamtest07] WITH PASSWORD=N'password07', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; CREATE USER [veeamtest07] FOR LOGIN [veeamtest07]'

Just copy the code and save it with a .SQL extension, after editing username/password as you please. You can run it on your instances using SQL Server Management Studio (SSMS) or good old SQLCMD. In this case you can run it using the following syntax:

SQLCMD -S <your_instance> -i script.sql

(Don’t forget, SQLCMD switches are case sensitive!)

 

 

8 thoughts on “New SQL Server SureBackup script in Veeam B&R v8

  1. Chris Lane

    Hi,

    I am having trouble getting this to work. When I manually run the SC query whilst the backed up VM is running (as masquerade IP 192.168.255.5 instead of the normal 192.168.1.5) I get Access is denied error 5.
    Firewalls are turned off, Veeam is running on a 201222 R2 VM and is patched up to and including yesterdays Update 3, the VM being tested is 2008 Std.
    I can ping the tested VM, I cannot “net use” – error 1311, no logon servers available.

    Any ideas?

    1. Danilo Post author

      Hi Chris, that’s likely due to the fact that in the virtual lab there is no domain controller able to process your logon.

      You can easily bypass the “SC Query” completely, just specify an instance when invoking the script.

  2. Anonymous

    To reiterate sp_MSForEachDB is not only undocumented it is also unsupported meaning it could be entirely removed from SQL Server in the future without notice. There have been bugs discovered numerous times where it skips databases completely. For anyone that is thinking about implementing and using this undocumented procedure should read this blog posting http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx

    1. Danilo Post author

      Thanks for your comment, I’ve added a note in the post.

      It should be noted anyway that even if the stored procedure wouldn’t work correctly, it wouldn’t be a big deal – the script will simply fail to “touch” the databases skipped by sp_MSForEachDB and this will be easily spotted in the logs.

      Of course developing a proper (better?) equivalent of this stored procedure is advisable; thanks for the link.

  3. Guy Schellens

    Nice post!

    The SQL checker script unfortunately contains another error in at least version 8.0.0.2021 when handling exceptions.in line 166 in the if statement, change:

    “If Not instanceList.Contains” to “if Not ExcludeList.Contains”

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*