How to configure a NetBackup policy to protect Microsoft SQL databases?

By | August 24, 2015

This post covers configuring NetBackup policy to backup/protect all databases on Microsoft SQL Server host. The configuration can be divided for two parts:

  1. Create a sql backup batch file on source (Microsoft SQL Server host).
  2. Create a NetBackup policy on Master Server.

Before showing how to do above parts, let's discuss some basic terminology used in this post:

  • batch file - The script that is used to back up or to restore SQL Server objects. The database agent performs all operations through a batch file. Batch files are typically stored in the install_path\dbext\mssql\ directory.
  • full backup - A complete backup of the database that contains all of the data files and the log file. (Note that a full backup does not truncate the transaction log.)
  • differential backup - A backup of the changed blocks since the last full backup.
  • transaction log backup - A backup of the inactive portion of the transaction log. Typically, this portion of the transaction log is truncated after it has been backed up successfully.
  • transaction log - An ongoing record of updates that were made to a database.
  • backup stripes - A data stream that is used for a backup or a restore of SQL Server objects. The user specifies the number of stripes for the backup. NetBackup performs a separate job each stripe that is specified.

There are also some requirements to backup Microsoft SQL Server by NetBackup. The most important are as follows:

  • NetBackup for SQL License added on Master Server.
  • NetBackup Client installed on Microsoft SQL Server host.
  • The SQL Server version supported by NetBackup.

For more information always please follow the NetBackup SQL Guide.

To create a batch file please follow the below steps:

      1. From the Windows Start menu, select Programs > Symantec NetBackup > NetBackup Agents > NetBackup MS SQL Client.
      2. When you are prompted to provide the logon parameters, click OK.How to configure a NetBackup policy to protect Microsoft SQL databases
      3. In the SQL Server connection properties dialog box, select the SQL Server host and instance that you want to log into.
      4. If the SQL Server host and instance use standard or mixed security, provide the SQL Server user ID and password or domain user/password.How to configure a NetBackup policy to protect Microsoft SQL databases 2
      5. Click Apply.
      6. Click Close.
      7. Select File > Backup SQL Server objects.How to configure a NetBackup policy to protect Microsoft SQL databases 3
      8. In the Backup Microsoft SQL Server Objects dialog box, in the left pane, select the database instance. How to configure a NetBackup policy to protect Microsoft SQL databases 4
        There are backup types as follows:

        1. Full - Create a full database backup.
        2. Full differential - Create a differential backup.
        3. Transaction log - Create a transaction log backup. This type of backup is only available for databases. When you select this type of backup, you then need to select a backup option from the Transaction log backup options list.
          • Back up and truncate transaction log - Back up the transaction log and remove the inactive part of the transaction log.
          • Back up transaction log, but do not truncate it - Back up a transaction log without truncating it.
          • Back up and restore tail log - Back up and recover the tail log from disk.
        4. Read/write filegroups - Create a backup of read or write filegroups in a database.
        5. Differential on read/write filegroups- Create a differential backup of read or write filegroups in a database.
        6. Create a template for partial backup - Create a backup of only the selected filegroups in a database.
        7. Create a template for partial differential backup - Create a differential backup of only the selected filegroups in a database.
      1. In the right pane, select one or more databases that you want to back up. I selected to backup all databases.How to configure a NetBackup policy to protect Microsoft SQL databases 5
      2. Select Save to create a batch file.
      3. Specify the batch file name and click Save.How to configure a NetBackup policy to protect Microsoft SQL databases 6
      4. You can check the batch file/or modify it. Close the file.How to configure a NetBackup policy to protect Microsoft SQL databases 7
      5. If you used windows domain account in 2 step, you need configure the logon account for the NetBackup services. NetBackup Client Service and the NetBackup Legacy Network Service must meet the following requirements:
          • The account has the fixed serverrole “sysadmin.”
          • Both services must use the same logon account.

        How to configure a NetBackup policy to protect Microsoft SQL databases 13

To create a NetBackup policy for SQL please follow the below steps:

      1. Start the NetBackup Administration Console.
      2. In the NetBackup Administration Console, select NetBackup Management > Policies. Then select Actions > New > New Policy. Type a name of policy and click OK.How to configure a NetBackup policy to protect Microsoft SQL databases 8
      3. In the Add New Policy dialog box, in the Policy type list, select MS-SQL-Server and Policy Storage (backup target).How to configure a NetBackup policy to protect Microsoft SQL databases 9
      4. On schedules tab, please create an automatic type schedule.How to configure a NetBackup policy to protect Microsoft SQL databases 10
      5. Add clients (SQL Server). How to configure a NetBackup policy to protect Microsoft SQL databases 11
      6. Specify a path to the batch file created earlier in this post.How to configure a NetBackup policy to protect Microsoft SQL databases 12
      7. Click OK to save the policy.

Testing the created policy

Now, let's start our policy manually.How to configure a NetBackup policy to protect Microsoft SQL databases test

Wow, it works! Microsoft SQL databases have been backed up. However a batch file consists of a series of operations (backups and restores) which by default are run in sequence. Let's change our script to run a backup of 10 databases at the same time. We need to modify the batch file and add the following parameter (batchsize 10, 10 is maximum):How to configure a NetBackup policy to protect Microsoft SQL databases test1aAnd now we can see there are more backup jobs at the same time:How to configure a NetBackup policy to protect Microsoft SQL databases test2

You can find some useful parameters in the NetBackup SQL guide. Just I wanted to show that you can always modify what and how should be backed up 🙂

Important: I showed how to perform the full backup of SQL databases. As mentioned earlier, logs are not truncated during this type of backup. If you need to truncate logs or backup logs,  please select type of backup: Transaction log --> Back up and truncate transaction log in step 8:How to configure a NetBackup policy to protect Microsoft SQL logs

You should create two batch files: the first one to backup databases, the second one to backup and truncate logs.How to configure a NetBackup policy to protect Microsoft SQL logs script

Author: Mariusz

Architect (~ 15 years experience based on passion...) with strong background as a System Administrator and Engineer. Focused on Data Center Solutions: Virtualization/Cloud Computing and Storage/Backup Systems. Currently living in Poland.