For those of you who’ve worked with NetApp and SnapManager for SQL Server, you will know can generate a scheduled backup as a SQL Agent Job. Simple, click through the GUI, selecting the Databases you want to backup, the schedule etc etc and a job is created with a command line script within it similar to that below:

"C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe" new-backup

–svr 'ServerToBackupName'

-d 'ServerToBackupName', '4', 'DBName1', 'DBName2', 'DBName3', 'DBName4'

-ver –verInst 'ServerToVerifyOnName' -mp

–mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint'

-RetainBackups 7 -lb -bksif -RetainSnapofSnapInfo 0 -trlog –mgmt standard

This script indicates the server in which the Databases reside upon for backing up, the number of databases to be backed up as well the list of database names you selected earlier in the GUI.

Now, this looks all relatively straight forward and makes perfect sense up until the point when you either create or drop a database from the server. This script does not pick up the fact that this has happened and will fail to snap any new databases and will no doubt fail trying to snap a database that no longer exists. So, every time you do this administration work you have the additional step of amending the job to match the current list of databases as it cannot update itself – how crazy is that!?!

I’m not sure about you I don’t like giving myself additional work to do so I set about rectifying this issue by creating a script that would mean you can create this job once and not have to worry about it again unless you specifically require to exclude a certain database by dynamically populating the Database list for you based on what is in sys.databases. This can be modified to include or exclude certain Databases i.e. system databases.

The script itself is straightforward, assigning the list of Databases to a variable and build up a command string to be ran via xp_cmdshell. Note:- There are certain security risks associated with enabling the xp_cmdshell feature so please make sure you’ve read and understood this before proceeding ?.

I’ve added comments to the script where necessary:

SET NOCOUNT ON;
-- Amend variables where necessary depending on how many DBs you have
-- Remembering the maximum recommendation for DBs on a LUN (from NetApp) is 35
DECLARE @strSQL        VARCHAR(2000), 
@strDBNamesVARCHAR(1000),
@intDBCntVARCHAR(5),
@strVerifServer VARCHAR(50),
@intRetainBkups INT
SELECT@strSQL        = '',
@strDBNames= '',
@strVerifServer = 'VerificationServerName',
@intRetainBkups = 8

--Get DB Names
SELECT @strDBNames = @strDBNames +''''+ NAME +''', '
FROM sys.databases
--WHERE database_id > 4
--Get DB count
SELECT @intDBCnt = CONVERT(VARCHAR(5),COUNT(*)) 
FROM sys.databases
--WHERE database_id > 4
--remove trailing comma, probably a fancier way but its quick and works
SET @strDBNames = LEFT(@strDBNames,LEN(@strDBNames)-1) 
--Make sure this string is all on one line!!!!
--Multi-line for readability
SET @strSQL = @strSQL + '
"C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe" new-backup  
–svr '''+@@SERVERNAME+'''
-d  '''+@@SERVERNAME+''', '''+@intDBCnt+''', '+@strDBNames+'
-ver  –verInst '''+@strVerifServer+''' -mp  
–mpdir ''C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint''
-RetainBackups  '+CONVERT(VARCHAR(5),@intRetainBkups)+' -lb -bksif -RetainSnapofSnapInfo 0 -trlog  –mgmt standard 
'
SELECT @strSQL --Output the text being ran, can comment out
EXEC xp_cmdshell @strSQL

There we have it. A NetApp snap backup script which will continue to work even when you go crazy and decide to add/drop as many databases as you can.

Hopefully there are other out there that can benefit from this script

Feel free to leave constructive criticism or even a positive comment but please don’t comment on things like “why have you used xp_cmdshell?” or “why did you do it so that you have to run a separate SET statement to remove trailing comma’s”, everyone has their way and this is invariably is mine ?

UPDATE (30th Nov 2011):

The latest versions of Snapmanager for SQL have addressed this issue and now if you select all DBs to backup it does not output all DB names into the job script as per the above. You can still use the script above to dynamically backup a selected set of databases by amending the select from sys.databases query so its not completed redundant!!!