Options to Create Backup Policies
To create backup policies with minimal required options use the following command:
- SQLsafeCmd Create-Policy Backup <policy_name> -IncludeInstance <name> -IncludeDatabases <db_name> [<db_name> ...] [options] [-BackupType <type> [backup options] [-BackupType <type> [backup options] ...]]
Option | Description |
|---|---|
| -ActionType <action> | Specifies the policy action. <action> - SqlAgent, SqlSafeBackupAgent, MonitorOnly. Note: the default is the SqlAgent action. |
| -RestrictRun [on|off] | [on|off] - Enable/Disable the policy. |
| -Description <description> | <description> - specifies the policy description. |
-IncludeInstance <instance_name> | <instance_name> - the instance name to include in policy. Option can be used one or more times. There must be NO OPTIONS between -IncludeInstance and -IncludeDatabases options. Note: this parameter is required. |
-IncludeDatabases <db_name> [<db_name> ...] | <db_name> [<db_name> ...] - one or more names of database(s) to include in policy. Special keywords: {All}, {AllSystem}, {AllUser}. Note: it is required for each -IncludeInstance option. |
-Exclude <db_name> [<db_name> ...] | <db_name> [<db_name> ...] - one or more names of database(s) to not backup. |
Email Options
You can set email notifications for your backup policy creations by using the following options:
| Options | Description |
|---|---|
| <email> - specifies where to send the notification. <on_event> - specifies the event on which to send the notification. Valid events are: OnError, OnSkip, OnCancel, OnWarning, OnSuccess. The default is no event. <frequency> - specifies the notification frequency. Valid values: Once, Always, Never. The default is never. |
Backup Options
The following backup option help you create backup policies:
Option | Description |
|---|---|
-BackupType <type> | Specifies backup type for policy. <type> - {Full, Differential, Diff, Log}. Option can be used one or more times. Note: the default type is "Full" with default settings. This parameter is optional. |
There are available options for -BackupType option:
| Option | Description |
|---|---|
| -CompressionLevel <level> | The compression level used for the backup. <level> - {ispeed, isize, 0, 1, 2, 3, 4}. Note: if the compression level is not specified, ispeed is the default. |
-EncryptionType <type> | The type of encryption used to encrypt the backup. <type> - {AES128, AES256}. |
-BackupEncryptionPassword <pwd> | <pwd> - specifies the password for encrypted backup file. |
-Verify <yes|no> | Verifies the backup set after backup is complete. |
-IncludeLogins <yes|no> | For backup, includes the database logins in the backup file. For restore, creates the logins from the backup file on the destination server. Note: this parameter is optional. |
-GenerateMap <yes|no> | Generates maps. Note: for InstantRestore and SQL virtual database. |
-Threads <number> | <number> - specifies the number of threads that should be used to distribute the backup process across multiple processors. |
-Checksum <yes|no> | Instructs SQL Server to generate backup checksums during a backup, or verify backup checksums during a verify or restore. Note: for SQL 2005 and later only. This parameter is optional. |
-ContinueAfterError <yes|no> | Instructs SQL Server to continue the operation despite encountering errors such as invalid checksums. Note: for SQL 2005 and later only. This parameter is optional. |
-CopyOnly <yes|no> | Specifies that the backup does not affect the normal sequence of backups. Note: for SQL 2005 and later only. This parameter is optional. |
-ReadWriteFileGroups <yes|no> | Instructs SQL Server to perform a partial backup, which includes the primary filegroup and any read-write secondary filegroups. Note: for SQL 2005 and later only. This parameter is optional. |
-TruncateTransactionLog <yes|no> | Removes inactive entries for transaction log in "Log" backup option. Optional. Note: by default, this option is enabled. |
-Location <type> | Specifies backup storage type. <type> - {Single File, TSM, Striped Files, Data Domain, S3 Cloud}. Note: The default value is "Single File". This parameter is optional. |
-Overwrite <yes|no> | Overwrites existing archive if one exists. Note: The default value is "no". This parameter is optional. |
-RetryWrites <interval> <retry_time> <total_time> | On a network file error, retry every <interval> seconds for up to <retry_time> seconds. Total retry time allowed is <total_time> minutes. |
-FailOnMirrorError <yes|no> | To abort backup if a mirror location reports a failure. Note: by default, this option is disabled. This parameter is optional. |
-MirrorFile <filename> | Specifies additional backup archive files to be used for mirroring backups. <filename> - specifies the backup archive files. Note: use once for each additional mirror. Up to two mirrors may be specified. |
-DeleteMirror <n><time_period> | After a backup successfully completes, delete mirrors that are older than the specified amount of time. <n> - amount of time. <time_period> - {minutes, hours, days, weeks, months}. There must be NO SPACE between <n> and <time_period>. E.g., -deletemirror 2hours. Note: if you use the space between <n> and <time_period>, the mirror filename will be automatically generated with the following pattern: <instancename>_<databasename>_<backuptype>_<timestamp>.safe where the <timestamp> is in UTC time and in the form of YYYYMMDDHHMM. |
-BackupFile <filename> | Specifies additional backup archive files to be used for striping backups. <filename> - specifies the backup archive files. Note: use once for each additional stripe. |
| -Delete <n><time_period> | After a backup successfully completes, delete archives that are older than the specified amount of time. <n> - amount of time. <time_period> - {minutes, hours, days, weeks, months}. There must be NO SPACE between <n> and <time_period>. E.g., -delete 2hours. Note: if you use the space between <n> and <time_period>, the backup archive filename will be automatically generated with the following pattern: <instancename>_<databasename>_<backuptype>_ <timestamp>.safe where the <timestamp> is in UTC time and in the form of YYYYMMDDHHMM. |
-UseAgentAccount <yes|no> | Specifies Agent account for accesing to files. Note: by default, the policy uses Agent account. This parameter is optional. |
-WindowsUsername <domain\user> | <domain\user> - specifies user name, used when writing to remote files during backup. |
-WindowsPassword <pwd> | <pwd> - specifies password. |
-BucketName <bucket_name> | <bucket_name> - specifies the cloud bucket name. |
-SecretKey <key> | <key> - specifies the cloud secret name. |
-AccessKey <key> | <key> - specifies the cloud access key. |
-FileSize <file_size> | <file_size> - specifies the cloud file size. |
-Region <region> | <region> - specifies the cloud region. |
| -StorageClass <storage_class> | <storage_class> - specifies the cloud storage class: Standard, Standard-IA, and OneZone-IA. Note: this setting is optional. The default value is Standard. |
Schedule Options
You can schedule the creation of backup policies by using the following options:
| Options | Description |
|---|---|
-Schedule <occurs_type> | Specifies the schedule type (occurs type). <occurs_type> - OnDemand, Daily, Weekly, Monthly. |
-Every <n> | Specifies how often the policy runs. It depends on the schedule type. For Daily: every <n> day(s). For Weekly: every <n> week(s). For Monthly: every <n> month(s). Note: the default value is "1". |
-Day <day> | <day> - specifies a day of the week or a day of a month. Note: it is valid only with Weekly, Monthly schedule types. Valid values for Weekly: MON - SUN and * (every day). SUN is default. Valid values for Monthly: MON - SUN, Weekday, WeekendDay, Day or number 1 - 31. Is required when -MonthDay option is used. |
-MonthDay <month_day> | Specifies how often the policy runs. <month_day> - FIRST, SECOND, THIRD, FOURTH, LAST use with -Day option. LASTDAY - the policy runs on the last day of the month. |
-StartTime <time> | <time> - specifies the time of day that the policy runs. Note: it is required with a "once" frequency. The default value is the current local time when policy creates. |
-EndTime <time> | <time> - specifies the time of day that the policy ends. Note: it is is not valid in a "once" frequency. The default value is the "23:59:59". |
-Frequency <n><time_period> | Specifies daily frequency (how often the policy runs within a day). <n> - amount of time. <time_period> - {hour, minute}. E.g., -Frequency 2hours. Special keyword: Once. E.g., -Frequency Once. |
-StartDate <date> | <date> - specifies the date that the policy starts. Note: the default value is the current date. It is optional. |
-EndDate <date> | <date> - specifies the last date that the policy is scheduled to run. Note: by default, the schedules have no ending date. It is optional. |
Note: you can optionally specify backup job occur schedule.
For detailed descriptions and available options, see the CLI Help (SQLsafeCmd help Create-Policy).