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!)