Page tree

SQL Safe provides you with CLI commands to help you manually  edit your backup policies.

Options to Edit Backup Policies

To edit 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] ...]]


The following options can also be used to edit backup policies:
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.
-NewName <policy_name><policy_name> - specifies a new name of the policy.

-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 editions by using the following options:

OptionsDescription


-MailTo <email> [<email> ...] <on_event> [<on_event> ...] [<frequency>]

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

Note: you can optionally specify email settings.

Backup Options

The following backup option help you edit backup policies:

OptionDescription

-BackupType <type> <on|off>

Specifies backup type for policy and Enable/Disable it.

 <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:

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

 -EnableMirroring <yes|no>

<yes|no> - Enable/Disable mirroring.

Note: 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.

Schedule Options

You can schedule the edition of backup policies by using the following options:

OptionsDescription

-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 Edit-Policy).