SQL Safe provides you with CLI commands to help you manually backup a database.



To backup databases use the following commands:

  • SQLsafeCmd Backup <db_name> [<db_name>] <backup_archive> [options]
  • SQLsafeCmd Backup <db_name> [<db_name>] TSM [options]
  • SQLsafeCmd Backup <db_name> [<db_name>] TRUNCATEONLY [options]



Where: 

ActionDescription
<db_name>

One or more names of database(s) to backup.

If a database name contains a space, the database name should be surrounded with "double quotes".

Special keywords may be used to backup multiple databases. These keywords are: {all}, {allsystem}, {alluser}.

<backup_archive>

Path to the backup archive.
Tsm

Use Tivoli Storage Manager (see below for special options).

TruncateOnly

Use this keyword to truncate the transaction log of the SQL Server database only.

Common Options

The following options help you perform backup operations:

OptionsDescription

-BackupDescription <desc>

<desc> - the description of the backup.
-BackupName <name><name> - the name of the backup.
-BackupType <type>

The backup type required.

<type> - {full, differential, log, file}.

-BckDstType <type>

The backup destination type is used for the backup.

<type> - {0, 1, 2, 3, 4, 5, 6, 7}.

where:

TypeDescription
0Single File
1Stripped Files
2Mirrored
3TSM
4Data Domain
5Amazon S3
6Azure Blob
7TSM Stripes

Note: the default value is "0".

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

-Exclude <db_name> [<db_name> ...]

<db_name> [<db_name> ...] - one or more names of database(s) to not backup.

-InstanceName <name>

<name> - the SQL server instance name. 

Note: it is not required if the instance is set as a default on the target server.

-NoTruncate

Do not truncate the transaction log. (Log backup only).

-Overwrite

Overwrite existing archive if one exists.

Note: if this option is omitted, the default behavior is to append.

-Server <hostname>

<hostname> - the hostname of the server hosting the SQL Server where the operation should be performed.

 Note: this option is required for accessing remote or clustered SQL Servers (where applicable).

-Verify

Verify the backup set after backup is complete.

-SectorTypePublic or Government based on Azure Sector.

Encryption Options

Encrypt your backups with the following options:

OptionsDescription

-EncryptionType <type>

The type of encryption used to encrypt the backup.

<type> - {AES128, AES256}.

-Password <pwd><pwd> - the non-encrypted password used to encrypt the backup.
-EncryptedBackupPassword <pwd><pwd> - the encrypted password used to encrypt the backup. (Used with EncryptionType).

Security Options

Secure your backups with the following options:

OptionsDescription
-NoPromptNever prompt for credentials even if necessary.
-SecurityModel <model>

The security model used to log into SQL Server.

<model> - {Integrated, SQL}.

Note: Integrated (Windows authentication) is the default.

-SqlUsername <username><username> - the SQL Server username. (SQL SecurityModel).
-SqlPassword <pwd><pwd> - the SQL Server password. (SQL SecurityModel).
-EncryptedSqlPassword <pwd><pwd> - the encrypted SQL Server password generated by EncryptSqlPassword action. (SQLSecurityModel).
-WindowsUsername <domain\user><domain\user> - the Windows user that will be used to read/write the backup archive.
-WindowsPassword <pwd><pwd> - the password for the Windows user.
-EncryptedWindowsPassword <pwd><pwd> - the encrypted password for the Windows user generated by EncryptWindowsPassword action.

Advanced Options

The following advanced options help you perform backup operations:

OptionsDescription
-ArgsFile <filename>

The path to a file containing command-line arguments.

<filename> - specifies the file that contains the command line arguments.

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

-Checksum

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.

-ContinueAfterError

Instructs SQL Server to continue the operation despite encountering errors such as invalid checksums.

Note: for SQL 2005 and later only.

-CopyOnly

Specifies that the backup does not affect the normal sequence of backups.

Note: for SQL 2005 and later only.

-DatabaseFilegroup <filegroup>

<filegroup> - the database logical filegroup.

Note: for file BackupType only.

-DatabaseFilename <filename>

<filename> - the database logical filename.

Note: for file BackupType only.

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

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

-FailOnMirrorError

To abort a backup if an error is encountered while writing to a mirror backup archive.

Note: the default behavior is to abort only if an error is encountered while writing to a primary backup archive.

-IncludeLogins

For backup, includes the database logins in the backup file.

For restore, creates the logins from the backup file on the destination server.

-ReportTLogFor backup, 'Yes' reports skipped t-log backups against databases that are in
simple mode with a SUCCESS status rather than SKIPPED.
-MailTo <email_address>

<email_address> - an email address(es) to send the notification via SMTP.

Note: multiple addresses may be separated by spaces, semicolons, or commas.

-MaxTransferSizeSpecifies the largest unit of transfer in bytes to be used between SQL Safe and the backup media.
The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB). This parameter is used to enable compression on TDE enabled databases only when the MaxTransferSize value is set to 65537 or higher. If omitted, the MaxTransferSize will be taken from the 'Transfer Limit' value set in the SQL Safe agent properties.
-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.

-NoSkip

Disables automatic skipping of databases that cannot be backed up, such as offline databases.

-NoStatus

Prevents status messages from being cached or sent to the Repository.

-ReadWriteFileGroups

Instructs SQL Server to perform a partial backup, which includes the primary filegroup and any read/write secondary filegroups.

Note: SQL 2005 and later only.

-RecoveryMode <mode> [-UndoFile<filename>]

Specifies the mode in which to leave the database after the operation is completed.

<mode> - NoRecovery, Standby.

Note: for Standby mode an undo file may be specified with the -UndoFile option.

-UndoFile <filename>

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

Note: for Standby recovery mode only.

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

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

-VDB Off

Do not optimize for quick access by SQL virtual database.

-Verbose

Displays SQL Server result text for both successful and failed backups.

Note: by default, it displays for failed backups only.

Tivoli Storage Manager (TSM) Options

There are TSM options for your backup operations:

OptionsDescription
-TsmClientOwnerName <name><name> - the client owner name.
-TsmClientOwnerPassword <pwd><pwd> - the client owner password.
-EncryptedTsmClientOwnerPassword <pwd><pwd> - the encrypted TSM client owner password.
-TsmConfigFile <filename><filename> - the configuration file location.
-TsmHighLevel <name><name> - the high level file specification (path).
-TsmLowLevel <name><name> - the low level file specification (file name).
-TsmTcpServerAddress <address><address> - the TCP/IP address for the TSM server.
-TsmTcpPort <port><port> - the TCP/IP port address for the TSM server.
-TsmManagementClass <name><name> - the management class to which the backup file will be bound.

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



IDERA | Products | Purchase | Support | Community | Resources | About Us | Legal
  • No labels