Creating backup policies for SQL Server databases

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.

1. In the left navigation pane, click Settings.
2. In the Settings page, click Policies.
3. Click New.
4. On the Name page, enter the policy name and description.
5. On 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 truncate the transaction logs	Select Full backup and Log backup. 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	Select Full backup. 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 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: 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.

      

      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. On 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> full backups option, and specify the number of Snapshot copies 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: 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 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 on 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 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' 

7. On 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. Optional: On 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. On 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 looks instead of using an internal database Snapshot copy (TABLOCK)	Select Limit the checks and obtain the looks 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.