Skip to main content
SnapCenter Software 4.6
A newer release of this product is available.

Create backup policies for SQL Server databases

Contributors netapp-soumikd
PDFs

You can create a backup policy for the resource or the resource group before you use SnapCenter to back up SQL Server resources, or you can create a backup policy at the time you create a resource group or backup a single resource.

What you will need

  • You must have defined your data protection strategy.

  • You must have prepared for data protection by completing tasks such as installing SnapCenter, adding hosts, identifying resources, and creating storage system connections.

  • You must have configured the host log directory for log backup.

  • You must have refreshed (discovered) the SQL Server resources.

  • If you are replicating Snapshot copies to a mirror or vault, the SnapCenter administrator must have assigned the storage virtual machines (SVMs) for both the source volumes and destination volumes to you.

    For information about how administrators assign resources to users, see the SnapCenter installation information.

  • If you want to run the PowerShell scripts in prescripts and postscripts, you should set the value of the usePowershellProcessforScripts parameter to true in the web.config file.

    The default value is false

About this task

A backup policy is a set of rules that governs how you manage and retain backups, and how frequently the resource or resource group is backed up. Additionally, you can specify replication and script settings. Specifying options in a policy saves time when you want to reuse the policy for another resource group.

Most of the fields on these wizard pages are self-explanatory. The following information describes some of the fields for which you might require guidance.

Steps

  1. In the left navigation pane, click Settings.

  2. In the Settings page, click Policies.

  3. Click New.

  4. In the Name page, enter the policy name and description.

  5. In the Backup Type page, perform the following steps:

    1. Choose the backup type:

      If you want to…​ Do this…​

      Back up the database files and transaction logs and to truncate the transaction logs

      1. Select Full backup and Log backup.

      2. Enter the maximum number of databases that should be backed up for each Snapshot copy.

        Note You must increase this value if you want to run multiple backup operations concurrently.

      Back up the database files

      1. Select Full backup.

      2. Enter the maximum number of databases that should be backed up for each Snapshot copy. Default value is 100

        Note You must increase this value if you want to run multiple backup operations concurrently.

      Back up the transaction logs

      Select Log backup.

    2. If you are backing up your resources by using another backup application, select Copy only backup.

      Keeping the transaction logs intact allows any backup application to restore the databases. You typically should not use the copy only option in any other circumstance.

      Note Microsoft SQL does not support the Copy only backup option together with the Full backup and Log backup option for secondary storage.

    3. In the Availability Group Settings section, perform the following actions:

      For this field…​ Do this…​

      Backup on preferred backup replica only

      Select this option to backup only on preferred backup replica. The preferred backup replica is decided by the backup preferences configured for the AG in the SQL Server.

      Select replicas for backup

      Choose the primary AG replica or the secondary AG replica for the backup.

      Backup priority (Minimum and Maximum backup priority)

      Specify a minimum backup priority number, and a maximum backup priority number that decide the AG replica for backup. For example, you can have a minimum priority of 10 and a maximum priority of 50. In this case, all the AG replicas with a priority more than 10 and less than 50 are considered for backup.

      Note By default, the minimum priority is 1 and maximum priority is 100.
      Note In cluster configurations, the backups are retained at each node of the cluster according to the retention settings set in the policy. If the owner node of the AG changes, the backups are taken according to the retention settings and the backups of the previous owner node will be retained. The retention for AG is applicable only at the node level.

    4. If you want to schedule the backup that you want to create with this policy, specify the schedule type by selecting either On demand, Hourly, Daily, Weekly, or Monthly.

      You can select one schedule type for a policy.

      backup settings
      Note You can specify the schedule (start date, end date, and frequency) for backup operation while creating a resource group. This enables you to create resource groups that share the same policy and backup frequency, but lets you assign different backup schedules to each policy.
      Note If you have scheduled for 2:00 a.m., the schedule will not be triggered during daylight saving time (DST).

  6. In the Retention page, depending on the backup type selected in the backup type page, perform one or more of the following actions:

    1. In the Retention settings for the up-to-the-minute restore operation section, perform one of the following actions:

      If you want to…​ Do this…​

      Retain only a specific number of Snapshot copies

      Select the Keep log backups applicable to last <number> days option, and specify the number of days to be retained. If you near this limit, you might want to delete older copies.

      Retain the backup copies for a specific number of days

      Select the Keep log backups applicable to last <number> days of full backups option, and specify the number of days to keep the log backup copies.

    2. In the Full backup retentions settings section for the On Demand retention settings, perform the following actions:

      For this field…​ Do this…​

      Total Snapshot copies to keep

      If you want to specify the number of Snapshot copies to keep, select Total Snapshot copies to keep.

      If the number of Snapshot copies exceeds the specified number, the Snapshot copies are deleted with the oldest copies deleted first.

      Note The maximum retention value is 1018 for resources on ONTAP 9.4 or later, and 254 for resources on ONTAP 9.3 or earlier. Backups will fail if retention is set to a value higher than what the underlying ONTAP version supports.
      Important By default, the value of retention count is set to 2. If you set the retention count to 1, the retention operation might fail because the first Snapshot copy is the reference Snapshot copy for the SnapVault relationship until a newer Snapshot copy is replicated to the target.

      Keep Snapshot copies for

      If you want to specify the number of days for which you want to keep the Snapshot copies before deleting them, select Keep Snapshot copies for.

    3. In the Full backup retentions settings section for the Hourly, Daily, Weekly and Monthly retention settings, specify the retention settings for the schedule type selected in Backup Type page.

      For this field…​ Do this…​

      Total Snapshot copies to keep

      If you want to specify the number of Snapshot copies to keep, select Total Snapshot copies to keep. If the number of Snapshot copies exceeds the specified number, the Snapshot copies are deleted with the oldest copies deleted first.

      Important You must set the retention count to 2 or higher if you plan to enable SnapVault replication. If you set the retention count to 1, the retention operation might fail because the first Snapshot copy is the reference Snapshot copy for the SnapVault relationship until a newer Snapshot copy is replicated to the target.

      Keep Snapshot copies for

      If you want to specify the number of days for which you want to keep the Snapshot copies before deleting them, select Keep Snapshot copies for.

      The log Snapshot copy retention is set to 7 days by default. Use Set-SmPolicy cmdlet to change the log Snapshot copy retention.

      This example sets the log Snapshot copy retention to 2:

      Set-SmPolicy -PolicyName 'newpol' -PolicyType 'Backup' -PluginPolicyType 'SCSQL' -sqlbackuptype 'FullBackupAndLogBackup' -RetentionSettings @{BackupType='DATA';ScheduleType='Hourly';RetentionCount=2},@{BackupType='LOG_SNAPSHOT';ScheduleType='None';RetentionCount=2},@{BackupType='LOG';ScheduleType='Hourly';RetentionCount=2} -scheduletype 'Hourly'

      SnapCenter retains Snapshot copies of the database

  7. In the Replication page, specify replication to the secondary storage system:

    For this field…​ Do this…​

    Update SnapMirror after creating a local Snapshot copy

    Select this option to create mirror copies of backup sets on another volume (SnapMirror).

    Update SnapVault after creating a Snapshot copy

    Select this option to perform disk-to-disk backup replication.

    Secondary policy label

    Select a Snapshot label.

    Depending on the Snapshot copy label that you select, ONTAP applies the secondary Snapshot copy retention policy that matches the label.

    Note If you have selected Update SnapMirror after creating a local Snapshot copy, you can optionally specify the secondary policy label. However, if you have selected Update SnapVault after creating a local Snapshot copy, you should specify the secondary policy label.

    Error retry count

    Enter the number of replication attempts that should occur before the process halts.

  8. In the Script page, enter the path and the arguments of the prescript or postscript that should be run before or after the backup operation, respectively.

    For example, you can run a script to update SNMP traps, automate alerts, and send logs.

    Note You must configure the SnapMirror retention policy in ONTAP so that the secondary storage does not reach the maximum limit of Snapshot copies.

  9. In the Verification page, perform the following steps:

    1. In the Run verification for following backup schedules section, select the schedule frequency.

    2. In the Database consistency check options section, perform the following actions:

      For this field…​ Do this…​

      Limit the integrity structure to physical structure of the database (PHYSICAL_ONLY)

      Select Limit the integrity structure to physical structure of the database (PHYSICAL_ONLY) to limit the integrity check to the physical structure of the database and to detect torn pages, checksum failures, and common hardware failures that impact the database.

      Supress all information messages (NO INFOMSGS)

      Select Supress all information messages (NO_INFOMSGS) to suppress all informational messages. Selected by default.

      Display all reported error messages per object (ALL_ERRORMSGS)

      Select Display all reported error messages per object (ALL_ERRORMSGS) to display all the reported errors per object.

      Do not check nonclustered indexes (NOINDEX)

      Select Do not check nonclustered indexes (NOINDEX) if you do not want to check nonclustered indexes. The SQL Server database uses Microsoft SQL Server Database Consistency Checker (DBCC) to check the logical and physical integrity of the objects in the database.

      Limit the checks and obtain the locks instead of using an internal database Snapshot copy (TABLOCK)

      Select Limit the checks and obtain the locks instead of using an internal database Snapshot copy (TABLOCK) to limit the checks and obtain locks instead of using an internal database Snapshot copy.

    3. In the Log Backup section, select Verify log backup upon completion to verify the log backup upon completion.

    4. In the Verification script settings section, enter the path and the arguments of the prescript or postscript that should be run before or after the verification operation, respectively.

  10. Review the summary, and then click Finish.