Page tree

SQL Safe provides you with CLI commands to help you manually edit log shipping policies.

Options to Edit Log Shipping Policies

To edit log shipping policies with minimal required options use the following command:

  • SQLsafeCmd Create-Policy LogShipping <policy_name> -InstanceName <name> -DatabaseName <primary_db_name> -NetworkPath <path> [options] -SecondaryDatabase <secondary_db_name> -TargetInstance <name> [secondary database options] [-SecondaryDatabase <secondary_db_name> -TargetInstance <name> [secondary database options] ...]


The following options can also be used to edit log shipping policies:
OptionDescription
-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.

-RestoreGracePeriod <n><time_period>

Specifies when SQLsafe should consider this log shipping policy compliant.

<time_period> - {hour, minute}

A log backup must occur within "n" minutes/hours of the scheduled run time.

-BackupGracePeriod <n><time_period>

   

Specifies when SQLsafe should consider this log shipping policy compliant.

<time_period> - {hour, minute}

A secondary's data is no more than "n" minutes/hours older the n the most recent log backup.

Email Options

You can set email notifications for your log shipping policy editions by using the following options:

OptionsDescription


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

<email> - specifies where to send notification.

<on_event> - specifies the event on which send 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.

Primary Database Options

The following primary database options help you edit log shipping policies:

OptionsDescription

-InstanceName <instance_name>

<instance_name> - specifies the instance on the primary database location.

Note: this parameter is required.

-DatabaseName <db_name>

<db_name> - specifies the primary database.
Note: this parameter is required.

-Server <server_name>

<server_name> - specifies the server on the primary database location.

Note: this parameter is optional.

Schedule Options

You can schedule the primary database options to edit log shipping policies by using the following options:

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

-Threads <number>

<number> - specifies the number of threads that should be used to distribute the backup process across multiple processors.

-UseAgentAccount <yes|no>

Specifies the Agent account for accessing to files.

Note: this parameter is optional.

By default, the policy uses Agent account.

-WindowsUsername <domain\user>

<domain\user> - specifies the user name, used when writing to remote files during backup.

-WindowsPassword <pwd><pwd> - specifies the password.

-NetworkPath <path>

<path> - specifies where to store your backup files for primary database.

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

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

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.

Note: you can optionally specify backup job occur schedule for primary database.

Secondary Database (s) Options

The following secondary database options help you edit log shipping policies:

OptionsDescription

-SecondaryDatabase <db_name>

<db_name> - specifies the "Secondary Database" name.

Note: it requires -TargetInstance option.

Second parameter can be used for removing "Secondary Database" from policy.

Note: by default, this parameter is "On".

 -TargetInstance <instance_name>

<instance_name> - specifies the target instance.

Note: this parameter is required.

-TargetServer <server_name>

<server_name> - specifies the target server.

Note: this parameter is optional.

-Inatialization <type> [-Backupfile <file_name>]

Specifies the secondary database initialization.

<type> - {None, Latest Backup, Create Copyonly, Selection}

"none" - Do not initialize. The database exists and has received most recent full backup of primary database.

"latest_backup" - Initialize with a newly generated full backup.

"create_copyonly" - Initialize with a newly generated copy-only full backup.

"selection" - Initialize with selected backups, option -Backupfile is required for this initialization type.

Note: The default value is "Create Copyonly".

This parameter is optional.

-BackupFile <file_name>

<file_name> - specifies backups for "Selection" database initialization type.

-RecoveryMode <mode>

Specifies the secondary database state after restore.

<mode> {NoRecovery, Standby}.

Option -DisconnectUsers is applicable with "Standby" recovery mode.

Note: the default value is "Standby" with enabled -DisconnectUsers option.

 This parameter is optional.

-UndoFile <filename>

<filename> - specifies the ABSOLUTE path to the undo filename.

Note: for Standby recovery mode only.

-DisconnectUsers <yes|no>

Disconnects all users from the target database before the restore operation begins.

Note: this parameter is optional.


Schedule Options

You can schedule the secondary database options to edit log shipping 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.

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

This parameter is optional.

-SetRestoreLocation <path>

<path> - specifies different location for restore.

Special keyword: "AsBackup" sets location the same as backup.

Note: the default value is the same location as backup.

This parameter is optional.

Note: you can optionally specify restore job occur schedule for secondary database.

For detailed descriptions and available options, see the CLI Help (SQLsafeCmd help Edit-Policy).