Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Auto Generate NetBackup script to backup SQL Server databases


By:   |   Last Updated: 2018-12-12   |   Comments   |   Related Tips: 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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools