Home > Articles > Data > Oracle

Figure 3.2 Steps for hot backup. The general steps involved in performing hot backup are shown in Figure 3.2. These general steps are used in writing hot backup scripts for Unix and Windows NT. The steps in Figure 3.2 are explained as follows. Step 1—Put the tablespace in the Backup mode and copy the data files. Where source is the folder that you want to backup and destination is the backup on the network drive. The arguments tell xcopy how to handle certain situations. For my script I used /Y to suppress prompting when overwriting files. If you do not include this the script will ask you before copying each file, that wouldn’t be automated. Backup User Profile Folders The purpose of this script is to backup user profile folders prior to enacting folder re-direction to a cloud solution such as OneDrive.The folders are backed up to the local workstation of the currently logged on user.NOTE: This script can be deployed as a package in SCCM or rem.

1. Cold Backup

Having the right backup and recovery procedures is the lifeblood of any database. Companies live on data, and, if that data is not available, the whole company collapses. As a result, it is the responsibility of the database administrator to protect the database from system faults, crashes, and natural calamities resulting from a variety of circumstances.

The choice of a backup and recovery mechanism depends mainly on the following factors:

• Database mode (ARCHIVELOG, NOARCHIVELOG)

• Size of the database

• Backup and recovery time

• uptime

• Type of data (OLTP, DSS, Data Warehouse).

The types of backup are

• Offline backup (Cold or closed database backup)

• Online backup (Hot or open database backup)

• Logical export

Logical exports create an export file that contains a list of SQL statements to recreate the database. Export is performed when the database is open and does not affect users work. Offline backups can only be performed when the database is shut down cleanly, and the database will be unavailable to users while the offline backup is being performed. Online backups are performed when the database is open, and it does not affect users work. The database needs to run in ARCHIVELOG mode to perform online backups.

The database can run in either ARCHIVELOG mode or NOARCHIVELOG mode. In ARCHIVELOG mode, the archiver (ARCH) process archives the redo log files to the archive destination directory. These archive files can be used to recover the database in the case of a failure. In NOARCHIVELOG mode, the redo log files are not archived.

When the database is running in ARCHIVELOG mode, the choice can be one or more of the following:

• Export

• Hot backup

• Cold backup

When the database is running in NOARCHIVELOG mode, the choice of backup is as follows:

• Export

• Cold backup

Cold Backup

Offline or cold backups are performed when the database is completely shutdown. The disadvantage of an offline backup is that it cannot be done if the database needs to be run 24/7. Additionally, you can only recover the database up to the point when the last backup was made unless the database is running in ARCHIVELOG mode.

The general steps involved in performing a cold backup are shown in Figure 3.1. These general steps are used in writing cold backup scripts for Unix and Windows NT.

Figure 3.1 Steps for cold backup.

The steps in Figure 3.1 are explained as follows.

Step 1—Generating File List

An offline backup consists of physically copying the following files:

• Data files

• Control files

• Init.ora and config.ora files

CAUTION

Backing up online redo log files is not advised in all cases, except when performing cold backup with the database running in NOARCHIVELOG mode. If you make a cold backup in ARCHIVELOG mode do not backup redo log files. There is a chance that you may accidentally overwrite your real online redo logs, preventing you from doing a complete recovery.

If your database is running in ARCHIVELOG mode, when you perform cold backup you should also backup archive logs that exist.

Before performing a cold backup, you need to know the location of the files that need to be backed up. Because the database structure changes day to day as more files get added or moved between directories, it is always better to query the database to get the physical structure of database before making a cold backup.

To get the structure of the database, query the following dynamic data dictionary tables:

• V$datafile Lists all the data files used in the database
• Backup the control file and perform a trace of the control file using

• Init.ora and config.ora Located under $ORACLE_HOME/dbs directory

Step 2—Shut down the database

You can shut down a database with the following commands:

Step 3—Perform a backup

In the first step, you generated a list of files to be backed up. To back up the files, you can use the Unix copy command (cp) to copy it to a backup location, as shown in the following code. You have to copy all files that you generated in Step 1.

You can perform the backup of the Init.ora and config.ora files as follows:

Step 4—Start the database

After the backup is complete, you can start the database as follows:

Hot Backup

An online backup or hot backup is also referred to as ARCHIVE LOGbackup. An online backup can only be done when the database is running in ARCHIVELOG mode and the database is open. When the database is running in ARCHIVELOG mode, the archiver (ARCH) background process will make a copy of the online redo log file to archive backup location.

An online backup consists of backing up the following files. But, because the database is open while performing a backup, you have to follow the procedure shown in Figure 3.2 to backup the files:

• Data files of each tablespace

• Archived redo log files

• Control file

• Init.ora and config.ora files

Figure 3.2 Steps for hot backup.

The general steps involved in performing hot backup are shown in Figure 3.2. These general steps are used in writing hot backup scripts for Unix and Windows NT.

The steps in Figure 3.2 are explained as follows.

Step 1—Put the tablespace in the Backup mode and copy the data files.

Assume that your database has two tablespaces, USERS and TOOLS. To back up the files for these two tablespaces, first put the tablespace in backup mode by using the ALTER statement as follows:

After the tablespace is in Backup mode, you can use the SELECT statement to list the data files for the USERS tablespace, and the copy (cp) command to copy the files to the backup location. Assume that the USERS tablespace has two data files—users01.dbf and users02.dbf.

The following command ends the backup process and puts the tablespace back in normal mode.

You have to repeat this process for all tablespaces. You can get the list of tablespaces by using the following SQL statement:

Step 2—Back up the control and Init.ora files.

To backup the control file,

You can copy the Init.ora file to a backup location using

Step 3—Stop archiving.

Archiving is a continuous process and, without stopping archiver, you might unintentionally copy the file that the archiver is currently writing. To avoid this, first stop the archiver and then copy the archive files to backup location. You can stop the archiver as follows:

The first command switches redo log file and the second command stops the archiver process.

Step 4—Back up the archive files.

To avoid backing up the archive file that is currently being written, we find the least sequence number that is to be archived from the V$LOG view, and then backup all the archive files before that sequence number. The archive file location is defined by the LOG_ARCHIVE_DEST_n parameter in the Init.ora file.

Step 5—Restart the archive process.

The following command restarts the archiver process:

Now you have completed the hot backup of database.

An online backup of a database will keep the database open and functional for 24/7 operations. It is advised to schedule online backups when there is the least user activity on the database, because backing up the database is very I/O intensive and users can see slow response during the backup period. Additionally, if the user activity is very high, the archive destination might fill up very fast.

Database Crashes During Hot Backup

There can be many reasons for the database to crash during a hot backup—a power outage or rebooting of the server, for example. If these were to happen during a hot backup, chances are that tablespace would be left in backup mode. In that case you must manually recover the files involved, and the recovery operation would end the backup of tablespace. It's important to check the status of the files as soon as you restart the instance and end the backup for the tablespace if it's in backup mode.

or

This statement lists files with ACTIVE status. If the file is in ACTIVE state, the corresponding tablespace is in backup mode. The second statement gives the tablespace name also, but this can't be used unless the database is open. You need to end the backup mode of the tablespace with the following command:

Logical Export

Export is the single most versatile utility available to perform a backup of the database, de-fragment the database, and port the database or individual objects from one operating system to another operating system.

Export backup detects block corruption

Though you perform other types of backup regularly, it is good to perform full export of database at regular intervals, because export detects any data or block corruptions in the database. By using export file, it is also possible to recover individual objects, whereas other backup methods do not support individual object recovery.

Export can be used to export the database at different levels of functionality:

• Full export (full database export) (FULL=Y)

• User-level export (exports objects of specified users) (OWNER=userlist)

• Table-level export (exports specified tables and partitions) (TABLES=tablelist)

• Transportable tablespaces (TABLESPACES=tools, TRANSPORT_TABLESPACE=y)

There are two methods of Export:

• Conventional Path (default)—Uses SQL layer to create the export file. The fact is that the SQL layer introduces CPU overhead due to character set, converting numbers, dates and so on. This is time consuming.

• Direct path (DIRECT=YES)—Skips the SQL layer and reads directly from database buffers or private buffers. Therefore it is much faster than conventional path.

We will discuss scripts to perform the full, user-level, and table-level export of database. The scripts also show you how to compress and split the export file while performing the export. This is especially useful if the underlying operating system has a limitation of 2GB maximum file limit.

Understand scripting

This chapter requires understanding of basic Unix shell and DOS batch programming techniques that are described in Chapter 2 'Building Blocks.' That chapter explained some of the common routines that will be used across most of the scripts presented here.

This book could have provided much more simple scripts. But, considering standardization across all scripts and the reusability of individual sections for your own writing of scripts, I am focusing on providing a comprehensive script, rather than a temporary fix. After you understand one script, it is easy to follow the flow for the rest of the scripts.

Related Resources

• Online Video $119.99

• eBook (Watermarked) $31.99

• Book $43.99

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, and Azure SQL Database 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 (,).

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.

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.

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.

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.

Compress

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

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

CopyOnly

Perform a copy-only backup.

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.

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.

CheckSum

Enable backup checksums.

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.

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

EncryptionAlgorithm

Specify the type of encryption.

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.

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.

NoRecovery

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

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.

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 (,).

Updateability

Select READ_ONLY/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.

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:

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:

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:

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:

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

Script Backup

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.

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.

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.

ExcludeLogShippedFromLogBackup

Exclude databases configured for Log Shipping, from log backups.

StringDelimiter

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

DatabaseOrder

Specify the database order.

DatabasesInParallel

Process databases in parallel.

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.

Execute

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

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 = 'Server1Backup',
@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 = 'Server1Backup, Server2Backup, Server3Backup, Server4Backup',
@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 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'

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

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

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

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

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

L. 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 FilesDPSAPPScommonlockbox',
@CleanupTime = 24

M. 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}'

N. 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}'

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

Script Backup Sql Server Express

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

Script Backup Restore Mysql Database

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.