SQL Server Backup

DatabaseBackup is the SQL Server Maintenance Solution’s stored procedure for backing up databases. DatabaseBackup is supported on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022, and Azure SQL Managed Instance.

Download

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts. The SQL Server Maintenance Solution is available on GitHub.

License

The SQL Server Maintenance Solution is free.

Parameters

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

Value	Description
SYSTEM_DATABASES	All system databases (master, msdb, and model)
USER_DATABASES	All user databases
ALL_DATABASES	All databases
AVAILABILITY_GROUP_DATABASES	All databases in availability groups
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES	All user databases that are not in availability groups
Db1	The database Db1
Db1, Db2	The databases Db1 and Db2
USER_DATABASES, -Db1	All user databases, except Db1
%Db%	All databases that have “Db” in the name
%Db%, -Db1	All databases that have “Db” in the name, except Db1
ALL_DATABASES, -%Db%	All databases that do not have “Db” in the name

Directory

Specify backup root directories, which can be local directories or network shares. If you specify multiple directories, then the backup files are striped evenly across the directories. Specify multiple directories by using the comma (,). If no directory is specified, then the SQL Server default backup directory is used.

Value	Description
NULL	Back up to the SQL Server default backup directory. This is the default.
C:\Backup	Back up to the directory C:\Backup.
C:\Backup, D:\Backup	Back up to the directories C:\Backup and D:\Backup.
\\Server1\Backup	Back up to the network share \\Server1\Backup.
\\Server1\Backup, \\Server2\Backup	Back up to the network shares \\Server1\Backup and \\Server2\Backup.
NUL	Backup to NUL.

DatabaseBackup creates a directory structure with server name, instance name, database name, and backup type under the backup root directory. If the database is a part of an availability group, then cluster name and availability group name are used instead of server name and instance name.

BackupType

Specify the type of backup: full, differential, or transaction log.

Value	Description
FULL	Full backup
DIFF	Differential backup
LOG	Transaction log backup

DatabaseBackup uses the SQL Server BACKUP command: BACKUP DATABASE for the full backup, BACKUP DATABASE WITH DIFFERENTIAL for the differential backup, and BACKUP LOG for the transaction log backup.

Verify

Verify the backup.

Value	Description
Y	Verify the backup.
N	Do not verify the backup. This is the default.

The Verify option in DatabaseBackup uses the SQL Server RESTORE VERIFYONLY command.

CleanupTime

Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted.

CleanupMode

Specify if old backup files should be deleted before or after the backup has been performed.

Value	Description
BEFORE_BACKUP	Delete old backup files before the backup has been performed.
AFTER_BACKUP	Delete old backup files after the backup and verify has been performed. If the backup or verify failed, then no backup files are deleted. This is the default.

Compress

Compress the backup. If no value is specified, then the backup compression default in sys.configurations is used.

Value	Description
NULL	Use the backup compression default in sys.configurations. This is the default.
Y	Compress the backup.
N	Do not compress the backup.

The Compress option in DatabaseBackup uses the COMPRESSION and NO_COMPRESSION options in the SQL Server BACKUP command.

CompressionAlgorithm

Specify the backup compression algorithm.

Value	Description
NULL	Use the backup compression algorithm in sys.configurations. This is the default.
MS_XPRESS	SQL Server backup compression
QAT_DEFLATE	Intel QuickAssist Technology (QAT) backup compression

The CompressionAlgorithm option in DatabaseBackup uses the COMPRESSION ... ALGORITHM option in the SQL Server BACKUP command.

CopyOnly

Perform a copy-only backup.

Value	Description
Y	Perform a copy-only backup.
N	Perform a normal backup. This is the default.

The CopyOnly option in DatabaseBackup uses the COPY_ONLY option in the SQL Server BACKUP command.

ChangeBackupType

Change the backup type if a differential or transaction-log backup cannot be performed.

Value	Description
Y	Change the backup type if a backup cannot be performed.
N	Skip the backup if a backup cannot be performed. This is the default.

DatabaseBackup checks differential_base_lsn in sys.master_files to determine whether a differential backup can be performed. If a differential backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a full backup performed instead.

DatabaseBackup checks last_log_backup_lsn in sys.database_recovery_status to determine whether a transaction log backup in full or bulk-logged recovery model can be performed. If a transaction log backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a differential or full backup performed instead.

BackupSoftware

Specify third-party backup software; otherwise, SQL Server native backup is performed.

Value	Description
NULL	SQL Server native backup (the default)
DATA_DOMAIN_BOOST	DELL EMC Data Domain Boost
LITESPEED	Quest LiteSpeed for SQL Server
SQLBACKUP	Red Gate SQL Backup Pro
SQLSAFE	Idera SQL Safe Backup

CheckSum

Enable backup checksums.

Value	Description
Y	Enable backup checksums.
N	Do not enable backup checksums. This is the default.

The CheckSum option in DatabaseBackup uses the CHECKSUM option in the SQL Server BACKUP command.

BlockSize

Specify the physical blocksize in bytes.

The BlockSize option in DatabaseBackup uses the BLOCKSIZE option in the SQL Server BACKUP command.

BufferCount

Specify the number of I/O buffers to be used for the backup operation.

The BufferCount option in DatabaseBackup uses the BUFFERCOUNT option in the SQL Server BACKUP command.

MaxTransferSize

Specify the largest unit of transfer, in bytes, to be used between SQL Server and the backup media.

The MaxTransferSize option in DatabaseBackup uses the MAXTRANSFERSIZE option in the SQL Server BACKUP command.

NumberOfFiles

Specify the number of backup files. The default is the number of backup directories and the maximum is 64 files.

MinBackupSizeForMultipleFiles

Specify a minimum backup size in MB, for when DatabaseBackup should back up to multiple files.

MaxFileSize

Specify a maximum backup file size in MB. DatabaseBackup will dynamically calculate the number of backup files.

CompressionLevel

Set the LiteSpeed, Red Gate SQL Backup Pro, or Idera SQL Safe Backup compression level.

In LiteSpeed, the compression levels 0 to 8 are supported. In Red Gate SQL Backup Pro, levels 0 to 4 are supported, and in Idera SQL Safe Backup, levels 1 to 4 are supported.

Description

Enter a description for the backup.

The Description option in DatabaseBackup uses the DESCRIPTION option in the SQL Server BACKUP command.

Threads

Specify the LiteSpeed, Red Gate SQL Backup Pro, or Idera SQL Safe Backup number of threads. The maximum number of threads is 32.

Throttle

Specify the LiteSpeed maximum CPU usage, as a percentage.

Encrypt

Encrypt the backup.

Value	Description
Y	Encrypt the backup.
N	Do not encrypt the backup. This is the default.

The Encrypt option in DatabaseBackup uses the ENCRYPTION option in the SQL Server BACKUP command.

EncryptionAlgorithm

Specify the type of encryption.

Value	Description
NULL	No encryption (the default)
RC2_40	RC2 40-bit encryption (LiteSpeed)
RC2_56	RC2 56-bit encryption (LiteSpeed)
RC2_112	RC2 112-bit encryption (LiteSpeed)
RC2_128	RC2 128-bit encryption (LiteSpeed)
TRIPLE_DES_3KEY	Triple DES encryption (SQL Server native encryption or LiteSpeed)
RC4_128	RC4 128-bit encryption (LiteSpeed)
AES_128	AES 128-bit encryption (SQL Server native encryption, LiteSpeed, Red Gate SQL Backup Pro, or Idera SQL Safe Backup)
AES_192	AES 192-bit encryption (SQL Server native encryption or LiteSpeed)
AES_256	AES 256-bit encryption (SQL Server native encryption, LiteSpeed, Red Gate SQL Backup Pro, or Idera SQL Safe Backup)

The EncryptionAlgorithm option in DatabaseBackup uses the ENCRYPTION and ALGORITHM options in the SQL Server BACKUP command.

ServerCertificate

Server certificate that is used to encrypt the backup.

The ServerCertificate option in DatabaseBackup uses the ENCRYPTION and SERVER CERTIFICATE options in the SQL Server BACKUP command.

ServerAsymmetricKey

Asymmetric key that is used to encrypt the backup.

The ServerAsymmetricKey option in DatabaseBackup uses the ENCRYPTION and SERVER ASYMMETRIC KEY options in the SQL Server BACKUP command.

EncryptionKey

Key that is used to encrypt the backup. This is used with LiteSpeed, Red Gate SQL Backup Pro, and Idera SQL Safe Backup.

ReadWriteFileGroups

Perform a backup of the primary filegroup and any read/write filegroups.

Value	Description
Y	Perform a backup of the primary filegroup and any read/write filegroups.
N	Perform a normal backup. This is the default.

The ReadWriteFileGroups option in DatabaseBackup uses the READ_WRITE_FILEGROUPS option in the SQL Server BACKUP command.

OverrideBackupPreference

Override the backup preference for availability groups. This option only applies to copy-only full backups and regular transaction log backups.

Value	Description
Y	Override the backup preference for availability groups.
N	Do not override the backup preference for availability groups. This is the default.

NoRecovery

Perform a backup of the tail of the log and leave the database in the RESTORING state.

Value	Description
Y	Perform a backup of the tail of the log.
N	Perform a normal backup. This is the default.

The NoRecovery option in DatabaseBackup uses the NORECOVERY option in the SQL Server BACKUP command.

URL

Specify the URL for backup to Azure Blob Storage.

The URL option in DatabaseBackup uses the URL option in the SQL Server BACKUP command.

Credential

Specify a CREDENTIAL for backup to Windows Azure Blob Storage.

The Credential option in DatabaseBackup uses the CREDENTIAL option in the SQL Server BACKUP command.

MirrorDirectory

Specify one or multiple directories to perform a mirrored backup.

The MirrorDirectory option in DatabaseBackup uses the MIRROR TO option in the SQL Server BACKUP command.

MirrorCleanupTime

Specify the time, in hours, after which the backup files are deleted in the mirror directories. If no time is specified, then no backup files are deleted.

By default backup files are deleted after each database is backed up and verified. Backup files are deleted only if the backup and verification of the database were successful.

DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted. This is to guarantee that you can always perform a point-in-time restore.

MirrorCleanupMode

Specify if old backup files in the mirror directory should be deleted before or after the backup has been performed.

Value	Description
BEFORE_BACKUP	Delete old backup files before the backup has been performed.
AFTER_BACKUP	Delete old backup files after the backup has been performed. This is the default.

MirrorURL

Specify the URL for a mirrored backup to Azure Blob Storage.

The MirrorURL option in DatabaseBackup uses the MIRROR TO URL option in the SQL Server BACKUP command.

AvailabilityGroups

Select availability groups. The keyword ALL_AVAILABILITY_GROUPS is supported. The hyphen character (-) is used to exclude availability groups, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

Value	Description
ALL_AVAILABILITY_GROUPS	All availability groups
AG1	The availability group AG1
AG1, AG2	The availability groups AG1 and AG1
ALL_AVAILABILITY_GROUPS, -AG1	All availability groups, except AG1
%AG%	All availability groups that have “AG” in the name
%AG%, -AG1	All availability groups that have “AG” in the name, except AG1
ALL_AVAILABILITY_GROUPS, -%AG%	All availability groups that do not have “AG” in the name

Updateability

Select READ_ONLY/READ_WRITE - databases.

Value	Description
ALL	READ_ONLY and READ_WRITE - databases. This is the default.
READ_ONLY	READ_ONLY - databases
READ_WRITE	READ_WRITE - databases

is_read_only in sys.databases is used to check if a database is READ_ONLY or READ_WRITE.

AdaptiveCompression

Automatically selects the optimal compression level based on CPU usage or Disk IO. This option is only available for LiteSpeed.

Value	Description
SIZE	Optimize the backup compression for size.
SPEED	Optimize the backup compression for speed.

ModificationLevel

Specify a percentage when a differential backup will be changed to a full backup. This option can only be used together with @ChangeBackupType = 'Y'.

DatabaseBackup checks allocated_extent_page_count and modified_extent_page_count in sys.dm_db_file_space_usage to calculate how much of a database that has been modified.

LogSizeSinceLastLogBackup

Specify a minimum size (MB) for the amount of log that has been generated since the last log backup. This option can only be used together with @TimeSinceLastLogBackup.

DatabaseBackup checks log_since_last_log_backup_mb in sys.dm_db_log_stats to dermine how much log that has been generated since the last log backup.

If the database is participating in an availability group as a secondary replica, the log will be backed up, regardless of this parameter.

TimeSinceLastLogBackup

Specify a minimum time, in seconds, since the last log backup. This option can only be used together with @LogSizeSinceLastLogBackup.

DatabaseBackup checks log_backup_time in sys.dm_db_log_stats to dermine when a transaction log has been backed up the last time.

If the database is participating in an availability group as a secondary replica, the log will be backed up, regardless of this parameter.

DataDomainBoostHost

Specify the name of the Data Domain server.

DataDomainBoostUser

Specify the name of the Data Domain user.

DataDomainBoostDevicePath

Specify the name and the path of the Data Domain storage unit.

DataDomainBoostLockboxPath

Specify the folder that contains the Data Domain lockbox file.

DirectoryStructure

Specify the backup sub-directory structure for databases that are not in an availability group.

You can use the following tokens:

Token	Description
ServerName	Server name
InstanceName	Instance name
ServiceName	Service name
DatabaseName	Database name
BackupType	Backup type
Partial	PARTIAL for partial backups
CopyOnly	COPY_ONLY for copy-only backups
Description	Backup description
MajorVersion	Major version
MinorVersion	Minor version
DirectorySeparator	The directory separator

Default directory structure: {ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}

Tokens that do not apply will be removed. E.g. the token {CopyOnly} (and the associated _) will be removed if it is not a copy-only backup.

If the parameter is set to NULL, no sub-directories will be created.

AvailabilityGroupDirectoryStructure

Specify the backup sub-directory structure for databases that are in an availability group.

You can use the following tokens:

Token	Description
ServerName	Server name
InstanceName	Instance name
ServiceName	Service name
ClusterName	Cluster name
AvailabilityGroupName	Availability group name
DatabaseName	Database name
BackupType	Backup type
Partial	PARTIAL for partial backups
CopyOnly	COPY_ONLY for copy-only backups
Description	Backup description
MajorVersion	Major version
MinorVersion	Minor version
DirectorySeparator	The directory separator

Default directory structure: {ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}

Tokens that do not apply will be removed. E.g. the token {CopyOnly} (and the associated _) will be removed if it is not a copy-only backup.

If the parameter is set to NULL, no sub-directories will be created.

FileName

Specify the file name for databases that are not in an availability group.

You can use the following tokens:

Token	Description
ServerName	Server name
InstanceName	Instance name
ServiceName	Service name
DatabaseName	Database name
BackupType	Backup type
Partial	PARTIAL for partial backups
CopyOnly	COPY_ONLY for copy-only backups
Description	Backup description
Year	Year
Month	Month
Day	Day
Week	Week
Hour	Hour
Minute	Minute
Second	Second
Millisecond	Millisecond
Microsecond	Microsecond
FileNumber	The file number when you are backing up to multiple - files
NumberOfFiles	The number of files when you are backing up to multiple files
FileExtension	The file extension
MajorVersion	Major version
MinorVersion	Minor version

Default file name: {ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}

Tokens that do not apply will be removed. E.g. the token {CopyOnly} (and the associated _) will be removed if it is not a copy-only backup.

AvailabilityGroupFileName

Specify the file name for databases that are in an availability group.

You can use the following tokens:

Token	Description
ServerName	Server name
InstanceName	Instance name
ServiceName	Service name
ClusterName	Cluster name
AvailabilityGroupName	Availability group name
DatabaseName	Database name
BackupType	Backup type
Partial	PARTIAL for partial backups
CopyOnly	COPY_ONLY for copy-only backups
Description	Backup description
Year	Year
Month	Month
Day	Day
Week	Week
Hour	Hour
Minute	Minute
Second	Second
Millisecond	Millisecond
Microsecond	Microsecond
FileNumber	The file number when you are backing up to multiple files
NumberOfFiles	The number of files when you are backing up to multiple files
FileExtension	The file extension
MajorVersion	Major version
MinorVersion	Minor version

Default file name: {ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}

Tokens that do not apply will be removed. E.g. the token {CopyOnly} (and the associated _) will be removed if it is not a copy-only backup.

FileExtensionFull

Specify the file extension for full backups.

By default "bak" is used for SQL Server native backups, "bak" is used for LiteSpeed, "sqb" is used for Red Gate SQL Backup Pro, and "safe" is used for Idera SQL Safe Backup.

FileExtensionDiff

Specify the file extension for differential backups.

By default "bak" is used for SQL Server native backups, "bak" is used for LiteSpeed, "sqb" is used for Red Gate SQL Backup Pro, and "safe" is used for Idera SQL Safe Backup.

FileExtensionLog

Specify the file extension for log backups.

By default "trn" is used for SQL Server native backups, "trn" is used for LiteSpeed, "sqb" is used for Red Gate SQL Backup Pro, and "safe" is used for Idera SQL Safe Backup.

Init

Specify whether the backup file should be overwritten.

Value	Description
Y	Overwrite the backup file.
N	Append the backup to the backup file. This is the default.

The Init option in DatabaseBackup uses the INIT option in the SQL Server BACKUP command.

Format

Specify whether a new media header should be created.

Value	Description
Y	Create a new media header.
N	Preserve the existing media header. This is the default.

The Format option in DatabaseBackup uses the FORMAT option in the SQL Server BACKUP command.

ObjectLevelRecoveryMap

Generate a map file during a backup for Object Level Recovery. This option is only supported in LiteSpeed.

Value	Description
Y	Generate a map file.
N	Do not generate a map file. This is the default.

ExcludeLogShippedFromLogBackup

Exclude databases configured for Log Shipping, from log backups.

Value	Description
Y	Exclude databases configured for Log Shipping, from log backups. This is the default.
N	Do not exclude databases configured for Log Shipping, from log backups.

DirectoryCheck

Check if the backup root directory exists.

Value	Description
Y	Check if the backup root directory exists. This is the default.
N	Do not check if the backup root directory exists.

BackupOptions

Options for backup to AWS S3 storage.

StringDelimiter

Specify the string delimiter. By default, the string delimiter is comma.

DatabaseOrder

Specify the database order.

Value	Description
NULL	The order that the databases have been specified in. Then ascending by the database name. This is the default.
DATABASE_NAME_ASC	Ascending by the database name
DATABASE_NAME_DESC	Descending by the database name
DATABASE_SIZE_ASC	Ascending by the database size
DATABASE_SIZE_DESC	Descending by the database size
LOG_SIZE_SINCE_LAST_LOG_BACKUP_ASC	Ascending by log_since_last_log_backup_mb in sys.dm_db_log_stats
LOG_SIZE_SINCE_LAST_LOG_BACKUP_DESC	Descending by log_since_last_log_backup_mb in sys.dm_db_log_stats

DatabasesInParallel

Process databases in parallel.

Value	Description
Y	Process databases in parallel.
N	Process databases one at a time. This is the default.

You can process databases in parallel by creating multiple jobs with the same parameters, and add the parameter @DatabasesInParallel = 'Y'.

LogToTable

Log commands to the table dbo.CommandLog.

Value	Description
Y	Log commands to the table.
N	Do not log commands to the table. This is the default.

Execute

Execute commands. By default, the commands are executed normally. If this parameter is set to N, then the commands are printed only.

Value	Description
Y	Execute commands. This is the default.
N	Only print commands.

Examples

A. Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

B. Back up all user databases to a network share, and verify the backup

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\Server1\Backup',
@BackupType = 'FULL',
@Verify = 'Y'

C. Back up all user databases across four network shares, and verify the backup

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\Server1\Backup, \\Server2\Backup, \\Server3\Backup, \\Server4\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@NumberOfFiles = 4

D. Back up all user databases to 64 files, using checksums and compression and setting the buffer count and the maximum transfer size

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@CheckSum = 'Y',
@BufferCount = 50,
@MaxTransferSize = 4194304,
@NumberOfFiles = 64

E. Back up all user databases to Azure Blob Storage, using compression

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@Credential = 'MyCredential',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y'

F. Back up all user databases to S3 storage, using compression

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@URL = 's3://myaccount.s3.us-east-1.amazonaws.com/mybucket',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@MaxTransferSize = 20971520,
@BackupOptions = '{"s3": {"region":"us-east-1"}}'

G. Back up the transaction log of all user databases, using the option to change the backup type if a log backup cannot be performed

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y'

H. Back up all user databases, using compression, encryption, and a server certificate.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'

I. Back up all user databases, using compression, encryption, and LiteSpeed, and limiting the CPU usage to 10 percent

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@BackupSoftware = 'LITESPEED',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@EncryptionKey = 'MyPassword',
@Throttle = 10

J. Back up all user databases, using compression, encryption, and Red Gate SQL Backup Pro

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@BackupSoftware = 'SQLBACKUP',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@EncryptionKey = 'MyPassword'

K. Back up all user databases, using compression, encryption, and Idera SQL Safe Backup

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@BackupSoftware = 'SQLSAFE',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@EncryptionKey = '8tPyzp4i1uF/ydAN1DqevdXDeVoryWRL'

L. Back up all user databases, using mirrored backups.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@MirrorDirectory = 'D:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@CleanupTime = 24,
@MirrorCleanupTime = 48

M. Back up all user databases, using Data Domain Boost.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@BackupType = 'FULL',
@CheckSum = 'Y',
@BackupSoftware = 'DATA_DOMAIN_BOOST',
@DataDomainBoostHost = 'Host',
@DataDomainBoostUser = 'User',
@DataDomainBoostDevicePath = '/DevicePath',
@DataDomainBoostLockboxPath = 'C:\Program Files\DPSAPPS\common\lockbox',
@CleanupTime = 24

N. Back up all user databases, with the default directory structure and file names.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}',
@AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}',
@FileName = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'

O. Back up all user databases, to a directory structure without the server name, instance name, cluster name, and availability group name.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@DirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}',
@AvailabilityGroupDirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}'

P. Back up all user databases, without creating any sub-directories.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@DirectoryStructure = NULL,
@AvailabilityGroupDirectoryStructure = NULL

Execution

You can execute the stored procedures from T-SQL job steps, or from CmdExec job steps with sqlcmd and the -b option.

SQL Server version	Job type
SQL Server 2008 and 2008 R2 on Windows	CmdExec job steps with sqlcmd and the -b option
SQL Server 2012, 2014, 2016, and 2017 on Windows	T-SQL job steps or CmdExec job steps with sqlcmd and the -b option
SQL Server 2017 on Linux	T-SQL job steps
Azure SQL Database Managed Instance	T-SQL job steps

There is a problem in SQL Server 2005, 2008 and 2008 R2 that a T-SQL job step stops executing after the first error. Use CmdExec job steps with sqlcmd and the -b option on these versions.

You can use the MaintenanceSolution.sql script to create the jobs. It will create CmdExec job steps with sqlcmd on SQL Server 2005, 2008 and 2008 R2, and T-SQL job steps on later versions.