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.
