SQL Server Backups

This article describes how you can use CloudBerry Backup CLI to create and customize Microsoft SQL Server backup and restore plans.

Create a Backup Plan {#back-up-a-file-or-folder}

Use the addBackupMsSqlPlan command to create and configure a new Microsoft SQL Server backup plan.

C:\Program Files\CloudBerryLab\CloudBerry Backup>cbb addBackupMsSqlPlan -?

Required Parameters {#required-parameters}

The following parameters are required to execute the addBackupMsSqlPlan command.

  • -n Specifies the backup plan name.

  • -a or -aid

    Specifies the name or ID of a storage account to where to upload your backups.

The following example illustrates how to use these parameters.

cbb addBackupMsSqlPlan -n "plan_name" -a "account"

Optional Parameters

You can use the following parameters when executing the addBackupMsSqlPlan command.

  • -instanceName Identifies the SQL Server instance containing the database(s) that you would like to back up.
  • -winAuth Indicates whether or not the selected SQL Server instance uses Windows authentication.
  • -userName and -password Use these parameters to specify SQL Server user credentials when the "-winAuth" parameter is set to "no".
  • -verify
    Enable this option to make the wizard automatically check whether the specified user account has all necessary permissions to restore the database.

The user must be assigned the sysadmin role in SQL Server.

  • -secure
    Use this option to select whether you need to secure an encrypted SSL/TLS connection between the database server and CloudBerry Backup.

The SQL Server instance must have a corresponding certificate installed, which becomes automatically trusted by the CloudBerry client application. See the following article for more information: How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console.

  • -dbSelectionType Specifies the databases to back up. The following values are supported:
  • "All" - backs up all databases available on the selected SQL Server instance, including user and system databases.
  • "User" - backs up only user databases that are available on the selected SQL Server instance. The backup will not include system databases (such as a master or model database).
  • "Selected" - enables you to select which database(s) you wish to back up by specifying the "-databases" parameter (see below).
  • -databases When the "-dbSelectionType" parameter is set to "Selected", you can use this parameter to specify which database(s) to include in your backup. You can list multiple databases by separating them using a semicolon.
  • -copyOnly Enable this option to prevent the backup from making any changes to the source database(s) which is the case when using native SQL Server backup mechanism. When this option is enabled, the database contents is copied to the backup without affecting the source database(s).
  • -sp Indicates whether or not to save the backup plan configuration to the backup storage along with the backup's contents.

The following example illustrates how to create a new plan to back up selected databases of a SQL Server instance that uses Windows authentication.

cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "Selected" -databases "AdventureWorks2017; custom" -copyOnly yes -sp yes

The following example illustrates how to create a new plan to back up all databases when a SQL Server instance uses its own authentication mechanism.

cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth no -userName "user" -password "password" -verify yes -dbSelectionType "All" -copyOnly yes -sp yes

Specifying the Encryption and Compression Settings

Use the following parameters to encrypt and/or compress the contents of your backup.

  • -c
    Indicates whether or not to compress files in the backup. For example:
  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -c yes

This can speed up uploading of your backup while it takes longer to process it before uploading.

  • -ea and -ep
    Encrypts the backup's contents using a specified algorithm (AES128, AES192 or AES256) and protects the backup with a password. For example:
  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -ea "AES192" -ep "password"

When updating an existing file-level backup that was not previously encrypted, the encryption will only apply to the newly uploaded files.

  • -sse
    Enables server-side encryption of the backup's contents (if a storage provider supports this feature).
    For example:
  cbb addBackupMsSqlPlan -n "plan_name" -a "Amazon_S3_account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -sse yes
  • -sia
    When using an Amazon storage account, enables an S3 Standard-Infrequent Access storage class.
    For example:
  cbb addBackupMsSqlPlan -n "plan_name" -a "Amazon_S3_account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -sia yes

Executing Custom Scripts

Use the following parameters to execute custom scripts before and/or after running a backup plan.

  • -preAction Specifies a command to execute before launching the backup.
  • -pac Indicates whether or not the backup service should continue backup processing if the pre-backup action failed.
  • -postAction Specifies a command to execute after completing the backup.
  • -paa Indicates whether or not the backup service should execute the post-backup action regardless of the backup processing result.

The following command creates a plan and specifies custom scripts to be executed before and after running this plan.

cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -preAction "C:\_Temp\script.cmd /F:ON /C" -pac yes -postAction "PowerShell.exe Start-Process cmd.exe exit" -paa no

See Adding Pre/Post Actions to learn more about using this feature.

Customizing Email Notification Settings

Use the following parameters to specify how the backup service should deliver email notifications after executing your backup plan and maintain the service log.

Before specifying these options, you need to configure the global notification and logging settings of the backup service. See Managing Email Notifications to learn more.

  • -notification Specifies whether to send email notifications only when a backup fails ("errorOnly") or in all cases ("on").
  • -dr Indicates whether the backup service should generate a detailed or simple report.
  • -winLog Indicates whether to add an entry to Windows Event Log only when the backup fails ("errorOnly") or in all cases ("on").

The following example illustrates how you can use these parameters.

cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -notification "errorOnly" -dr yes -winLog "on"

Customizing Retention Settings

Use the following options to configure the retention settings of your backup plan.

  • -purge Makes a backup service delete file versions that were modified or uploaded to your backup storage more than a certain number of days ago. The following values are supported:
  • "no" - indicates that data is stored for indefinite period.
  • "d (day)" - specifies for how many days to store your data.
  • "w (week)" - specifies for how many weeks to store your data.
  • "m (month)" - specifies for how many months to store your data.
  • -delayPurge Specifies the maximum number of versions that a backup service should keep in the storage for each file. The following values are supported:
  • "no" - enables immediate removal of files after they have been deleted in a source storage.
  • "d (day)" - specifies for how many days to keep locally deleted files in the backup storage.
  • "w (week)" - specifies for how many weeks to keep locally deleted files in the backup storage.
  • "m (month)" - specifies for how many months to keep locally deleted files in the backup storage.
  • -keep Specifies the maximum number of versions that a backup service should keep in the storage for each file.
  • -keepLastVersion Indicates whether to keep the last version of each file in a backup storage at all times, regardless of any other retention settings.

The following example illustrates how you can specify these parameters.

cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -purge 1week -delayPurge 1week -keep 5 -keepLastVersion yes

See Managing Application Settings to learn how to configure the global retention policy settings of a backup service.

Customizing Scheduling Settings

Use the following parameters to enable your backup plan to run automatically on schedule.

  • -at

    Specifies the time at which the backup plan should run. When other scheduling parameters are not defined, specifying only the time indicates that the backup plan should run at this time on the day of this plan's creation.

This parameter is required if you need to run your backup plan on a recurrent basis and specify corresponding scheduling options described below.
Instead of using the "-at" parameter, you can use the "-occurs" parameter (see below) to run the backup plan once in a specified number of hours or days.

You can specify both the date and time if you do not need to run your backup plan on a recurring basis, for example:

  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -at "5/28/2018 6:00 PM"
  • -every Enables you to run the backup plan on a recurring basis. The following values are supported:

  • "day"
    Enables running the backup plan every day. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -at "6:00 PM" -every day
    
  • "week"
    Enables running the backup plan every week. Use the "-weekday" parameter to specify one or more weekdays. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -at "6:00 PM" -every week -weekday "su, mo"
    
  • "month"
    Enables running the backup plan on a monthly basis. When no additional parameters are specified, the backup plan will run every month on the first Sunday by default. You can use the "-weekNumber" and "-weekday" parameters to specify a custom monthly schedule. For example, the following plan will be executed on a third Monday of every month.

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -at "6:00 PM" -every month -weekNumber "third" -weekday "mo"
    
    • The following values are supported for the "-weekNumber" parameter: "first", "second", "third", "fourth", "penultimate", "last".
    • The following values are supported for the "-weekday" parameter: "su", "mo", "tu", "we", "th", "fr", "sa".
  • "dayOfMonth" Not supported.

  • "real-time"
    Enables the backup service to monitor the source files in the background and update the backup in a storage in real time when any changes have been made to the source files. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -at "6:00 PM" -every real-time
    
  • -occurs
    When the "-every" parameter is set to "day" (see above), indicating that the backup plan should run on a daily basis, you can make the backup plan run once in a certain number of hours or days within a specified time period by using the "-occurs" parameter instead of "-at".

If the "-every" parameter is set to "day", specifying both the "-occurs" and "-at" parameters at once will result in an error.

The following values are supported for the "-occurs" parameter:

  • "day" - enables running the backup plan once in a specified number of days.

  • "hour" - enables running the backup plan once in a specified number of hours.

    After specifying this parameter, use the "-occurValue" parameter to specify how many hours or days should pass before each run.

    In addition, you can use the "-dailyFrom" and "-dailyTill" parameters to specify the time range within which the recurrence should take effect. These parameters accept a time-span value in the following format: "dd.hh:mm:ss".

    The following example illustrates how to make a backup plan run every two hours between 6:00 AM and 6:00 PM every day.

  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -every day -occurs hour -occurValue 2 -dailyFrom "00.06:00:00" -dailyTill "00.18:00:00"
  • -runMissed Set this parameter to "yes" to make the backup plan run automatically when computer starts up if a scheduled run was missed for any reason.

Scheduling a Differential Backup

Use the following parameters to enable running a differential backup automatically on schedule.

  • -at Diff

    Specifies the time at which the backup plan should run. When other differential backup scheduling parameters are not defined, specifying only the time indicates that the differential backup should run at this time on the day of this plan's creation.

This parameter is required if you need to run your backup plan on a recurrent basis and specify corresponding scheduling options described below.
Instead of using the "-atDiff" parameter, you can use the "-occursDiff" parameter (see below) to run the differential backup once in a specified number of hours or days.

You can specify both the date and time if you do not need to perform a differential backup on a recurring basis, for example:

  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atDiff "5/28/2018 6:00 PM"
  • -everyDiff Enables you to run a differential backup on a recurring basis. The following values are supported:

  • "day"
    Enables running the backup plan every day. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atDiff "6:00 PM" -everyDiff day
    
  • "week"
    Enables running the differential backup every week. Use the "-weekdayDiff" parameter to specify one or more weekdays. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atDiff "6:00 PM" -everyDiff week -weekdayDiff "su, mo"
    
  • "month"
    Enables running the differential backup on a monthly basis. When no additional parameters are specified, the backup plan will run every month on the first Sunday by default. You can use the "-weekNumberDiff" and "-weekdayDiff" parameters to specify a custom monthly schedule. For example, the following plan will be executed on a third Monday of every month.

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atDiff "6:00 PM" -everyDiff month -weekNumberDiff "third" -weekdayDiff "mo"
    
    • The following values are supported for the "-weekNumberDiff" parameter: "first", "second", "third", "fourth", "penultimate", "last".
    • The following values are supported for the "-weekdayDiff" parameter: "su", "mo", "tu", "we", "th", "fr", "sa".
  • "dayOfMonth" Not supported.

  • "real-time"
    Enables the backup service to monitor the source files in the background and update the backup in a storage in real time when any changes have been made to the source files. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atDiff "6:00 PM" -everyDiff real-time
    
  • -occursDiff
    When the "-everyDiff" parameter is set to "day" (see above), indicating that the differential backup should run on a daily basis, you can make the differential backup run once in a certain number of hours or days within a specified time period by using the "-occursDiff" parameter instead of "-atDiff".

If the "-everyDiff" parameter is set to "day", specifying both the "-occursDiff" and "-atDiff" parameters at once will result in an error.

The following values are supported for the "-occursDiff" parameter:

  • "day" - enables running the backup plan once in a specified number of days.

  • "hour" - enables running the backup plan once in a specified number of hours.

    After specifying this parameter, use the "-occurValueDiff" parameter to specify how many hours or days should pass before each run.

    In addition, you can use the "-dailyFromDiff" and "-dailyTillDiff" parameters to specify the time range within which the recurrence should take effect. These parameters accept a time-span value in the following format: "dd.hh:mm:ss".

    The following example illustrates how to make a backup plan run every two hours between 6:00 AM and 6:00 PM every day.

  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -everyDiff day -occursDiff hour -occurValueDiff 2 -dailyFromDiff "00.06:00:00" -dailyTillDiff "00.18:00:00"

Scheduling a Transaction Log Backup

SQL Server performs backup and restore operations in accordance with a recovery model defined for the database, specifying how transactions are logged, whether or not this log requires (and allows) backups, as well as what kinds of restore operations are available.

There are three recovery models available: simple, full, and bulk-logged. Typically, a database uses a full or simple recovery model. You can switch a database to using another recovery model at any time in the SQL Server settings.

If you wish to back up transaction logs, please ensure that your database is set to use the Full or Bulk recovery model.

Use the following parameters to enable running a transaction log backup automatically on schedule.

  • -at TLog

    Specifies the time at which the backup plan should run. When other transaction log backup scheduling parameters are not defined, specifying only the time indicates that the transaction log backup should run at this time on the day of this plan's creation.

This parameter is required if you need to run your backup plan on a recurrent basis and specify corresponding scheduling options described below.
Instead of using the "-atTLog" parameter, you can use the "-occursTLog" parameter (see below) to run the transaction log backup once in a specified number of hours or days.

You can specify both the date and time if you do not need to perform a transaction log backup on a recurring basis, for example:

  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atTLog "5/28/2018 6:00 PM"
  • -everyTLog Enables you to run a transaction log backup on a recurring basis. The following values are supported:

  • "day"
    Enables running the backup plan every day. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atTLog "6:00 PM" -everyTLog day
    
  • "week"
    Enables running the transaction log backup every week. Use the "-weekdayTLog" parameter to specify one or more weekdays. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atTLog "6:00 PM" -everyTLog week -weekdayTLog "su, mo"
    
  • "month"
    Enables running the transaction log backup on a monthly basis. When no additional parameters are specified, the backup plan will run every month on the first Sunday by default. You can use the "-weekNumberTLog" and "-weekdayTLog" parameters to specify a custom monthly schedule. For example, the following plan will be executed on a third Monday of every month.

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atTLog "6:00 PM" -everyTLog month -weekNumberTLog "third" -weekdayTLog "mo"
    
    • The following values are supported for the "-weekNumberTLog" parameter: "first", "second", "third", "fourth", "penultimate", "last".
    • The following values are supported for the "-weekdayTLog" parameter: "su", "mo", "tu", "we", "th", "fr", "sa".
  • "dayOfMonth" Not supported.

  • "real-time"
    Enables the backup service to monitor the source files in the background and update the backup in a storage in real time when any changes have been made to the source files. For example:

    cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -atTLog "6:00 PM" -everyTLog real-time
    
  • -occursTLog
    When the "-everyTLog" parameter is set to "day" (see above), indicating that the transaction log backup should run on a daily basis, you can make the transaction log backup run once in a certain number of hours or days within a specified time period by using the "-occursTLog" parameter instead of "-atTLog".

If the "-everyTLog" parameter is set to "day", specifying both the "-occursTLog" and "-atTLog" parameters at once will result in an error.

The following values are supported for the "-occursTLog" parameter:

  • "day" - enables running the backup plan once in a specified number of days.

  • "hour" - enables running the backup plan once in a specified number of hours.

    After specifying this parameter, use the "-occurValueTLog" parameter to specify how many hours or days should pass before each run.

    In addition, you can use the "-dailyFromTLog" and "-dailyTillTLog" parameters to specify the time range within which the recurrence should take effect. These parameters accept a time-span value in the following format: "dd.hh:mm:ss".

    The following example illustrates how to make a backup plan run every two hours between 6:00 AM and 6:00 PM every day.

  cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -everyTLog day -occursTLog hour -occurValueTLog 2 -dailyFromTLog "00.06:00:00" -dailyTillTLog "00.18:00:00"

Additional Options

Use the "-tag" parameter to specify custom additional information for your backup plan. For example:

cbb addBackupMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbSelectionType "All" -tag "Custom info"

The following parameters define the standard output format for this command.

  • -json Send the status to stdout in a JSON format.
  • -xml Send the status to stdout in an XML format.

Edit a Backup Plan {#section-file-level-plans}

Use the editBackupMsSqlPlan command to customize an existing SQL Server backup plan.

C:\Program Files\CloudBerryLab\CloudBerry Backup>cbb editBackupMsSqlPlan -?

The following example illustrates how to rename a backup plan.

cbb editBackupMsSqlPlan -n "old_name" -nn "new_name"

The following example illustrates how to disable encryption for a backup plan.

cbb editBackupMsSqlPlan -n "plan_name" -ed

The following example illustrates how to disable a general schedule defined for a backup plan.

cbb editBackupMsSqlPlan -n "plan_name" -sd

The following example illustrates how to disable a differential backup schedule.

cbb editBackupMsSqlPlan -n "plan_name" -sdDiff

The following example illustrates how to disable a transaction log backup schedule.

cbb editBackupMsSqlPlan -n "plan_name" -sdTLog

See the previous section of this document to learn about other parameters available for editing a backup plan using the editBackupMsSqlPlan command.

Create a Restore Plan

Use the addRestoreMsSqlPlan command to create and configure a new SQL Server restore plan.

C:\Program Files\CloudBerryLab\CloudBerry Backup>cbb addRestoreMsSqlPlan -?

Required Parameters {#required-parameters}

The following parameters are required to execute the addRestoreMsSqlPlan command.

  • -n Specifies the name of a created restore plan.
  • -a or -aid Specifies the name or ID of a storage account where your backups are stored.
  • -instanceName Specifies the target SQL Server instance to which to restore selected database(s).
  • -dbin Specifies the source SQL Server instance whose database(s) to restore.
  • -dbn Specifies the database to restore from a specified SQL Server instance. When the "-dbnn" parameter is not specified (see below), this database is restored to a database with the same name. You can use the "-ov" parameter (see below) to specify whether or not to replace the target database with a restored version if both these databases have identical names.

Optional Parameters

You can use the following parameters to configure your SQL Server restore plan.

  • -dbnn Specifies a new name for a restored database. Use this parameter if you do not need to restore to an original database.

  • -dp and -lp Use these parameters to specify the directories to which to restore SQL Server data and log files.

  • -fm

    Specifies the name of a restored SQL Server database file. You can use the %DATABASENAME% template to automatically assign the original database's name to this file.

  • -ov

    Indicates whether or not to overwrite existing database files with their restored versions if both these databases have identical names.

  • -cc

    Indicates whether the backup service should close all existing connections to restored databases.

  • -rt and -rpt
    Use the "-rt" parameter to specify whether to restore a latest version of your SQL Server data that is available in a specified backup storage at this moment, or a latest version available at a specific point in time. Possible values: "LatestVersion" (the default value) or "PointInTime".

CloudBerry backups include only files that were modified since the previous backup date. As a result, selecting this option will restore only those file versions that were modified before the most recent backup and are not yet available in the destination folder.

When the "-rt" parameter is set to "PointInTime", you should specify the "-rpt" parameter as well, indicating a required point in time.

  • -ep Specifies the password to restore an encrypted backup. These files will not be restored unless you have specified a valid encryption password.

Connection Settings

Use the following parameters to enable the backup service to connect to a Microsoft SQL Server instance.

  • -winAuth Indicates whether or not the selected SQL Server instance uses Windows authentication.
  • -userName and -password Use these parameters to specify SQL Server user credentials when the "-winAuth" parameter is set to "no".
  • -secure
    Use this option to select whether you need to secure an encrypted SSL/TLS connection between the database server and CloudBerry Backup.

The SQL Server instance must have a corresponding certificate installed, which becomes automatically trusted by the CloudBerry client application. See the following article for more information: How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console.

Examples

The following example illustrates how you can specify the parameters described above.

cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -dbnn "AdventureWorks2017_2" -ov yes -dp "C:\SQL\Data" -lp "C:\SQL\Logs" -fm "%DATABASENAME%_restored" -cc yes -ep "password"

The following command creates a plan for restoring the latest version of a SQL Server database that is available in the backup storage at a specified point in time.

cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -rt PointInTime -rpt "4/27/2018 3:00:00 PM"

Customizing Scheduling Settings

Use the following parameters to enable your restore plan to run automatically on schedule.

  • -at

    Specifies the time at which the restore plan should run. When other scheduling parameters are not defined, specifying only the time indicates that the restore plan should run at this time on the day of this plan's creation.

This parameter is required if you need to run your restore plan on a recurrent basis and specify corresponding scheduling options described below.
Instead of using the "-at" parameter, you can use the "-occurs" parameter (see below) to run the restore plan once in a specified number of hours or days.

You can specify both the date and time if you do not need to run your restore plan on a recurring basis, for example:

  cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -at "5/28/2018 6:00 PM"
  • -every Enables you to run the restore plan on a recurring basis. The following values are supported:

  • "day"
    Enables running the restore plan every day. For example:

    cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -at "6:00 PM" -every day
    
  • "week"
    Enables running the restore plan every week. Use the "-weekday" parameter to specify one or more weekdays. For example:

    cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -at "6:00 PM" -every week -weekday "su, mo"
    
  • "month"
    Enables running the restore plan on a monthly basis. When no additional parameters are specified, the restore plan will run every month on the first Sunday by default. You can use the "-weekNumber" and "-weekday" parameters to specify a custom monthly schedule. For example, the following plan will be executed on a third Monday of every month.

    cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -at "6:00 PM" -every month -weekNumber "third" -weekday "mo"
    
    • The following values are supported for the "-weekNumber" parameter: "first", "second", "third", "fourth", "penultimate", "last".
    • The following values are supported for the "-weekday" parameter: "su", "mo", "tu", "we", "th", "fr", "sa".
  • "dayOfMonth" Not supported.

  • "real-time"
    Enables the backup service to monitor the restored files in the background and update the restored data in a local storage in real time when any changes have been made to the source files in the cloud. For example:

    cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -at "6:00 PM" -every real-time
    
  • -occurs
    When the "-every" parameter is set to "day" (see above), indicating that the restore plan should run on a daily basis, you can make the restore plan run once in a certain number of hours or days within a specified time period by using the "-occurs" parameter instead of "-at".

If the "-every" parameter is set to "day", specifying both the "-occurs" and "-at" parameters at once will result in an error.

The following values are supported for the "-occurs" parameter:

  • "day" - enables running the restore plan once in a specified number of days.

  • "hour" - enables running the restore plan once in a specified number of hours.

    After specifying this parameter, use the "-occurValue" parameter to specify how many hours or days should pass before each run.

    In addition, you can use the "-dailyFrom" and "-dailyTill" parameters to specify the time range within which the recurrence should take effect. These parameters accept a time-span value in the following format: "dd.hh:mm:ss".

    The following example illustrates how to make a restore plan run every two hours between 6:00 AM and 6:00 PM every day.

  cbb addRestoreMsSqlPlan -n "plan_name" -a "account" -instanceName "MY-PC\SQLEXPRESS" -winAuth yes -dbin "MY-PC\SQLEXPRESS" -dbn "AdventureWorks2017" -every day -occurs hour -occurValue 2 -dailyFrom "00.06:00:00" -dailyTill "00.18:00:00"