Skip to main content
SnapCenter Software 5.0
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.

Before you begin

  • 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 Snapshots 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.

  • For SnapMirror Business Continuity (SM-BC), for more information on prerequisites and limitations refer Object limits for SnapMirror Business Continuity.

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.

    The SCRIPTS_PATH is defined using the PredefinedWindowsScriptsDirectory key located in the SMCoreServiceHost.exe.Config file of the plug-in host.

    If needed, you can change this path and restart SMcore service. It is recommended that you use the default path for security.

    The value of the key can be displayed from swagger through the API: API /4.7/configsettings

    You can use the GET API to display the value of the key. SET API is not supported.

  • SnapLock

    • If 'Retain the backup copies for a specific number of days' option is selected, then the SnapLock retention period must be lesser than or equal to the mentioned retention days.

      Specifying a Snapshot locking period prevents deletion of the Snapshots until the retention period expires. This could lead to retaining a larger number of Snapshots than the count specified in the policy.

      For ONTAP 9.12.1 and below version, the clones created from the SnapLock Vault Snapshots as part of restore will inherit the SnapLock Vault expiry time. Storage admin should manually cleanup the clones post the SnapLock expiry time.

      Note Primary SnapLock settings are managed in SnapCenter backup policy and the secondary SnapLock settings are managed by ONTAP.

Step 1: Create Policy Name

  1. In the left navigation pane, select Settings.

  2. In the Settings page, select Policies.

  3. Select New.

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

Step 2: Configure backup options

  1. Choose your backup type

Full Backup and Log Backup

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.

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

Back up the database files.

  1. Select Full backup.

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

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

Back up the transaction logs. . Select Log backup.

Copy Only Backup

  1. 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.

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

    1. 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.

    2. Select replicas for backup.

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

    3. Select 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.

      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.

  2. Schedule the backup frequency for this policy. Specify the schedule type by selecting either On demand, Hourly, Daily, Weekly, or Monthly.

    You can only select one schedule type for a policy.

    Backup settings screen.
    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).

Step 3: Configure retention settings

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:

Specific number of copies

Retain only a specific number of Snapshots.

  1. 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.

Specific number of days

Retain the backup copies for a specific number of days.

  1. 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.

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

    1. Specify total number of Snapshots to keep

      1. To specify the number of Snapshots to keep, select Total Snapshot copies to keep.

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

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 is the reference Snapshot for the SnapVault relationship until a newer Snapshot is replicated to the target.
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.

  1. Length of time to keep Snapshots

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

  2. If you want to specify the Snapshot locking period, select Snapshot copy locking period and select days, months, or years.

    Snaplock retention period should be less than 100 years.

  1. 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.

    1. Specify total number of Snapshots to keep

      1. To specify the number of Snapshots to keep, select Total Snapshot copies to keep. If the number of Snapshots exceeds the specified number, the Snapshots 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 is the reference Snapshot for the SnapVault relationship until a newer Snapshot is replicated to the target.

  1. Length of time to keep Snapshots

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

  2. If you want to specify the Snapshot locking period, select Snapshot copy locking period and select days, months, or years.

    SnapLock retention period should be less than 100 years.

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

This example sets the log Snapshot retention to 2:

Example 1. Show Example

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

Step 4: Configure replication settings

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

Update SnapMirror

Update SnapMirror after creating a local Snapshot copy.

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

    This option should be enabled for SnapMirror Business Continuity (SM-BC) or for SnapMirror Sync (SM-S).

    During secondary replication, the SnapLock expiry time loads the primary SnapLock expiry time. Clicking the Refresh button in the Topology page refreshes the secondary and primary SnapLock expiry time that are retrieved from ONTAP.

    See View SQL Server backups and clones in the Topology page.

Update SnapVault

Update SnapVault after creating a Snapshot copy.

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

    During secondary replication, the SnapLock expiry time loads the primary SnapLock expiry time. Clicking the Refresh button in the Topology page refreshes the secondary and primary SnapLock expiry time that are retrieved from ONTAP.

    When SnapLock is configured only on the secondary from ONTAP known as SnapLock Vault, clicking the Refresh button in the Topology page refreshes the locking period on the secondary that is retrieved from ONTAP.

    For more information on SnapLock Vault see Commit Snapshot copies to WORM on a vault destination

    See View SQL Server backups and clones in the Topology page.

Secondary Policy Label

  1. Select a Snapshot label.

Depending on the Snapshot label that you select, ONTAP applies the secondary Snapshot 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

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

Step 5: Configure script settings

  1. 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 The prescripts or postscripts path should not include drives or shares. The path should be relative to the SCRIPTS_PATH.
    Note You must configure the SnapMirror retention policy in ONTAP so that the secondary storage does not reach the maximum limit of Snapshots.

Step 6: Configure verification settings

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:

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

      1. 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.

    2. Suppress all information messages (NO INFOMSGS)

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

    3. Display all reported error messages per object (ALL_ERRORMSGS)

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

    4. Do not check nonclustered indexes (NOINDEX)

      1. 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.

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

      1. 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.

  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.

    Note The prescripts or postscripts path should not include drives or shares. The path should be relative to the SCRIPTS_PATH.

Step 7: Review summary

  1. Review the summary, and then select Finish.