Auto Generate NetBackup script to backup SQL Server databases

By: Alejandro Cobar | Updated: 2018-12-12 | Comments (4) | Related: > Backup

Problem

Sometimes companies opt to use third party solutions to manage their SQL Server database backups. One of these third-party solutions is NetBackup (by Veritas), which is a very good solution, but sometimes it might not play very well in certain circumstances. If you create a generic script, so that NetBackup takes a backup of all your databases, it will do exactly that without a hiccup. However, errors might be encountered if a database is in an offline mode (for whatever reason) or if you have database snapshots. For situations like this, you must go to your script and manually exclude all of the databases that apply. This sounds like a tedious thing, right? Well, this tip aims to fix that by automatically creating a custom NetBackup script that can be easily modified and managed right from SQL Server.

Solution

Here's an example of a NetBackup script that takes a full backup of all databases.

OPERATION BACKUP
DATABASE $ALL
SQLHOST "MSSQLEXAMPLE.DOMAIN.COM"
BROWSECLIENT "MSSQLEXAMPLE"
NBSERVER "NETBACKUPEXAMPLE.DOMAIN.COM"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

Here is a similar script, but this time it excludes database "test".

OPERATION BACKUP
DATABASE $ALL
EXCLUDE "test"
SQLHOST "MSSQLEXAMPLE.DOMAIN.COM"
BROWSECLIENT "MSSQLEXAMPLE"
NBSERVER "NETBACKUPEXAMPLE.DOMAIN.COM"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

Steps to generate NetBackup script using a SQL Server Agent Job

You have to create a script that contains the logic to specify only the databases you want to include in your NetBackup script. The script has the following form. This particular script excludes database snapshots (and tempdb of course).

SET NOCOUNT ON
DECLARE @DBName nvarchar(50)

DECLARE dbname_cursor CURSOR FOR 
SELECT name from sys.databases where source_database_id is null and name <> 'tempdb'
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'OPERATION BACKUP'
SELECT 'DATABASE '+CHAR(34)+@dbname+CHAR(34)
SELECT 'SQLHOST '+CHAR(34)+'MSSQLEXAMPLE'+CHAR(34)
SELECT 'NBSERVER '+CHAR(34)+'NETBACKUPEXAMPLE.DOMAIN.COM'+CHAR(34)
SELECT 'MAXTRANSFERSIZE 6'
SELECT 'BLOCKSIZE 7'
SELECT 'NUMBUFS 2'
SELECT 'ENDOPER TRUE'
SELECT ''
FETCH NEXT FROM dbname_cursor 
INTO @DBName
END 
CLOSE dbname_cursor 
DDEALLOCATE dbname_cursor

After you adjust the above script to fit your needs, you can add this to a job step to produce the NetBackup script that will be flawless. Then save the above code in a file called full_script.sql

Then run the following SQLCMD code to generate file full_default.bch which will contain the commands for NetBackup. First, adjust the location of where you put the full_script.sql file and also where you want to create the full_default.bch file.

SQLCMD -h-1 -i "C:\Program Files\Veritas\NetBackup\DbExt\MsSql\full_script.sql" -o "C:\Program Files\Veritas\NetBackup\DbExt\MsSql\Full_Default.bch" -W

Here's sample output of what the job will produce. This generated the NetBackup code to backup all system databases (excluding tempdb) and the "test" database.

OPERATION BACKUPDATABASE "master"
SQLHOST "MSSQLEXAMPLE"
NBSERVER "NETBACKUPEXAMPLE.DOMAIN.COM"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

OPERATION BACKUP
DATABASE "model"
SQLHOST "MSSQLEXAMPLE"
NBSERVER "NETBACKUPEXAMPLE.DOMAIN.COM"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

OPERATION BACKUP
DATABASE "msdb"
SQLHOST "MSSQLEXAMPLE"
NBSERVER "NETBACKUPEXAMPLE.DOMAIN.COM"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

OPERATION BACKUP
DATABASE "test"
SQLHOST "MSSQLEXAMPLE"
NBSERVER "NETBACKUPEXAMPLE.DOMAIN.COM"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
EENDOPER TRUE

Full Script to Generate NetBackup Code

Here is the full code to create the job described in the steps above. You will have to modify the paths of the destination of the output script and the generator script, in case you want them in different paths. The job specifies the 'sa' as owner, but you can change it to whatever you want.

USE [msdb]
GO

/****** Object:  Job [SDBA - Generate full backup script]    Script Date: 11/1/2018 8:08:01 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11/1/2018 8:08:01 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Generate full backup script', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'Generates the full backup script to be used by the NetBackup policy', 
      @category_name=N'[Uncategorized (Local)]', 
      @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Generate full backup script]    Script Date: 11/1/2018 8:08:01 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Generate full backup script', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'CmdExec', 
      @command=N'SQLCMD -h-1 -i "C:\Program Files\Veritas\NetBackup\DbExt\MsSql\full_script.sql" -o "C:\Program Files\Veritas\NetBackup\DbExt\MsSql\Full_Default.bch" -W', 
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly Full', 
      @enabled=1, 
      @freq_type=8, 
      @freq_interval=64, 
      @freq_subday_type=1, 
      @freq_subday_interval=0, 
      @freq_relative_interval=0, 
      @freq_recurrence_factor=1, 
      @active_start_date=20180417, 
      @active_end_date=99991231, 
      @active_start_time=175900, 
      @active_end_time=235959, 
      @schedule_uid=N'0913821a-0f28-41e6-ac27-1344f6dd198a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Next Steps

Here's the official NetBackup documentation, , in case you want to go deeper and explore the options this vendor offers.
You can modify the script to generate the backup script for just the databases you want to cover.
For automation purposes, you can modify the schedule of the job to fit your needs.
For some reason, the script doesn't allow the use of wild cards or regular expressions to address these types of scenarios. Perhaps it is something that will be addressed in a future release of the product.

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-12-12

Thursday, May 28, 2020 - 8:17:55 AM - Zufo	Back To Top (85796)
This is very good example. Do you have the same process for restore operations? I want to initiate restores from a sql server query, like your backup script.