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

12 thoughts on “How to configure a NetBackup policy to protect Microsoft SQL databases?

    1. Mariusz Post author

      Hi Morten,

      Yes, I will write about it as well. However, still there are a lot of NBU environments based on 7.1/7.5 version...

      btw. did you attend at the NBU Appliance 5330 training in Reading this year? Your name sounds familiar 🙂

      Reply
  1. Morten Seeberg

    I did yes, leaving for London Tuesday to attend EMEA NBU User Conference as a partner.

    Reply
  2. Sharique

    Could you please let us know how to perform restore of sql database

    Reply
  3. Niranjan

    As mentioned by you if we create 2 batch fie( one for full backup and one for transaction log), do we need to create a separate policy for each or else we can add both batch files in one Policy(In Backup Selection)

    Reply
    1. Mariusz Post author

      Hi,

      you do not need to create the second backup policy. However, generally the sql logs are backuped up more often (for example 4 times per day) so the second policy with another scheduler could be a good choice.

      Reply
  4. herry

    Hi,

    Good posting. I have 2 questions:

    1. What is difference between automatic backup vs application backup?
    2. Which one of the above actually control the image retention?

    thanks

    Reply
    1. Mariusz Post author

      Hi Herry,

      Automatic backup controls when a backup job should be started but Application backup controls: when a backup is allowed and also specifies retention as well. So you should have at least one Automatic and one Application Schedule type in your SQL policy. I wrote a little bit about it in post here.

      Reply
  5. Shashank

    Hi Mariusz,

    First, I would like to appreciate the efforts you have taken for writing this blog with lots of explanation and screenshots.
    It cleared most of my queries as I am new to using Netbackup however there are few more which are left; I hope you would get some time to reply back:
    We are using Symantec Netbackup 7.6.0.1 and SQL 2012 SP1; we have created two bkp scripts to perform FULL BKP and TRN log backup.
    Now I would like to understand few points:
    1) Can we take FULL BKP weekly and schedule FULL Differential on daily basis?
    2) If we take FULL Differential, will there be any changes required on TRN Log script or new script will be needed to perform FULL Differential?
    Please help.

    Regards,
    Shashank

    Reply
    1. Mariusz Post author

      Hi,

      ad 1) Yes, you can.
      ad 2) You need to create a new script for differential backup.

      Reply
  6. Shashank

    Hi Mariusz,

    I hope you are doing well.
    Can you share the steps or article which will help me setup email notification once the SQL 2012 backup completes?
    We are currently using Netbackup 7.7.2 with SQL 2012.

    Regards,
    Shashank

    Reply

Leave a Reply to herry Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.