SureBackup script to test a MS-SQL server for each database

[larry]

I created a SureBackup script to test a MS-SQL server for each database during Sure backup. When the sever comes up in the lab I needed to be sure all the databases loaded. The steps are easy see below. If you don’t have the sqlcmd just install “SQL Server Management Studio Express” on your Veeam server. If you have multi instances then use ip/instance.

Create a bat file call “testDatabase.bat”, mine is in c:\backup

Code: Select all

@Echo Off
sqlcmd -U Veeam_Lab_user -P Password -S %1  -d %2 -Q "quit" >>sqllog.txt
rem Using SQL Authentication, create SQL with denied read denied write to all databases
rem results append to log sqllog.txt
rem sqlcmd -S %1  -d %2 -Q "quit">>sqllog.txt
REM using Windows Authentication

rem sqlcmd -S GR-VEEAM\VEEAM -E -d "veeamBackup" -Q "quit"  >>sqllog.txt
rem pass server/instance 

if not errorlevel 1 goto next1
echo SQLCMD returned %errorlevel% to the command shell

echo == An error occurred 
goto theend

:next1
echo Found Database
exit /B 0

:theend
exit /B 1

REM use below to test and see error code - place in it owns bat file. 
REM call testdatabase.bat /Z
REM echo %ERRORLEVEL%
REM IF ERRORLEVEL 1 echo "Error"

Now in the application group in Veeam create a test script.

Name: Databasetotest (using the name helps reading the reports and real time logs)
Path: C:\backup\testdatabase.bat
Arguments: %vm_ip% databasename

Repeat group for each database name

Veeam thinks you could do to make better
1) Allow edit of predefined tests arguments, was easy to make this as a pull down but couldn’t edit arguments ( database name )
2) Some way to write results to the Veeam log, this way I could include database size, record count or what ever.
3) Just place in next version.

Hope you enjoy. now I am real sure all my database backups worked, I use this live with no issues with a TB of info.

[Gostev]

Hi Larry, this is really cool stuff, thanks for sharing this with everyone!
But I noticed in above some SQLCMD calls still have REM before them?

[larry]

one you use for SQL Authentication ( you supply password and user ) the other for Windows Authentication, you give the user who runs the veeam service the rights to the database then no password or user is needed. This line is when using Windows Authentication

Code: Select all

rem sqlcmd -S %1  -d %2 -Q "quit">>sqllog.txt

then rem out line 2

the bottom rems is to create a second bat file to test the first file outside of veeam. It will display the exit code so you know you wrote it right.

the line

Code: Select all

rem sqlcmd -S GR-VEEAM\VEEAM -E -d "veeamBackup" -Q "quit"  >>sqllog.txt

is to test on the veeam server just so you can see it working.
I use this to test a sql server ( GR-VEEAM) that used an instance ( VEEAM in this case ).

you can remove all rem lines and all echo lines and it still works.

you can test by clicking the leave application group ruuning box, when group is all up then open command line on the veeam server, run this bat file. replace %1 with you server new IP address that veeam knows about - the real time report shows this IP and %vm_ip% sends it to the bat. replace %2 with database name. You should also be able to replace %1 with the real server name and see the same result.

If you use the sql express default install on veeam then open a commanline and type

Code: Select all

sqlcmd -S yourveeamservername\VEEAM -E -d "veeamBackup" -Q "quit"

it should test that the veeam database is there, test using yourveeamservername\wrongname and you should see error returned.

[Gostev]

Ah, thanks for the clarification! I am not big SQL guy so that definitely helps to understand the script.

[davidb1234]

We have run in to several situations where we needed to restore something and have discovered that the underlying backup was corrupted in some way. On all of these VMs we run Surebackup jobs regularly and they succeed. We even run SQLchecker scripts but this is limited since it only detects if the database is mounted(this is the script Veeam support will provide). We have run in to situations where a database is corrupted in a backup and will still mount but will not pass a DBCC CHECKDB due to corruption. Our trust in the consistency of Veeam backups has greatly diminished as of late.

Anyone have any good scripts to verify NTFS file structure like a chkdsk script that can be used with Surebackup?

Anyone have any better SQL scripts that will actually run a DBCC CHECKDB on the databases rather than just check to see that it is mounted?

The surebackup feature has not really detected any kind of corruption and seems like a waste of time and resources in the months we have been using it.

In all cases Veeam support blames CBT.

[Gostev]

In theory, you should be able to invoke just about any command or script in SureBackup script. Just depends on your requirements and what specifically you want tested.

[rharding]

Hi,
can anyone tell me how to specify dynamic ports instead of 1433 for testing an sql server in a virtual lab please

ie: I have named instances configured to use dynamic ports on my sql server ?

eg. instead of "%vm_ip% aaa.bbb.ccc.ddd 1433"

[Vitaliy S.]

Hi Rick,

Have you tried to adjust the script above? As far as I know sqlcmd does not require any ports to connect to the required SQL Server instances.

Thanks!

[rharding]

Hi Vitaliy, this is what I am asking, I need to replace 1433 with something else to tell the script to use dynamic ports

SQL server uses i'ts browser on UDP 1434 to find instances, but what do I need to put in the VEEAM test script when my SQL server is using dynamic ports please ?

[Vitaliy S.]

Rick, I don't believe there is a way to specify the range of ports in the script used as a default verification tool. What I'm suggesting is adopting the script above (posted by Larry), that uses sqlcmd command and does not use any ports at all. Have you tried running it in your SB job?