Auto Generate NetBackup script to backup SQL Server databases


By:   |   Updated: 2018-12-12   |   Comments (4)   |   Related: More > 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)[email protected]+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 @[email protected], @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 @[email protected], @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.


Last Updated: 2018-12-12


get scripts

next tip button



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

View all my tips
Related Resources





Comments For This Article




Friday, June 19, 2020 - 4:36:25 PM - Eric Prévost-Dansereau Back To Top (86024)

Fully script netBackup restore is not fun.  I did it to do log shipping through NetBackup instead of SQL native backup.

To create the restore script, you need to find the name of the backup image you need to restore.  To find it, you can use bplist.exe:

"C:\Program Files\Veritas\NetBackup\bin\bplist.exe" -C [source client] -t 15 -S [NB Master Server] -R \

there is parameters to set a time range.  You may see duplicates too.


Thursday, May 28, 2020 - 10:54:09 PM - Alejandro Cobar Back To Top (85800)

Unfortunately no, I don't have one for restores. I didn't have enough time to play around with NetBackup to see if it had an api to obtain the backup images metadata so that a script can be built around it.

At this point, I'm not even sure if it is something that can be done in an automatic way. At most, you get the script that NetBackup generates after manually selecting what you wish to restore.


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.


Friday, February 14, 2020 - 3:13:25 AM - nurgul guneri Back To Top (84462)

Hi,

This is very good example, Do you have same way for restore operation. I want to initiate restore from sql server query, like your backup script.

Best Regards



download


Recommended Reading

Simple script to backup all SQL Server databases

Script to retrieve SQL Server database backup history and no backups

How to monitor backup and restore progress in SQL Server

Backup to multiple files for faster and smaller SQL Server files

Changing the default SQL Server backup folder





get free sql tips
agree to terms


Learn more about SQL Server tools