SQL Server Backup

DatabaseBackup is the SQL Server Maintenance Solution’s stored procedure for backing up databases. DatabaseBackup is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

Download

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts.

License

The SQL Server Maintenance Solution is free.

Parameters

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, and ALL_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
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

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.

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

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.

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)
HYPERBAC	Red Gate SQL HyperBac
LITESPEED	NetVault LiteSpeed for SQL Server
SQLBACKUP	Red Gate SQL Backup
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.

CompressionLevel

Set the NetVault LiteSpeed for SQL Server, Red Gate SQL Backup, or Idera SQL safe backup compression level.
In NetVault LiteSpeed for SQL Server, the compression levels 0 to 8 are supported. In Red Gate SQL Backup, 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 NetVault LiteSpeed for SQL Server, Red Gate SQL Backup, or Idera SQL safe backup number of threads. The maximum number of threads is 32.

Throttle

Specify the NetVault LiteSpeed for SQL Server 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.

EncryptionType

Specify the type of encryption.

Value	Description
NULL	No encryption (the default)
RC2-40	RC2 40-bit encryption (NetVault LiteSpeed for SQL Server)
RC2-56	RC2 56-bit encryption (NetVault LiteSpeed for SQL Server)
RC2-112	RC2 112-bit encryption (NetVault LiteSpeed for SQL Server)
RC2-128	RC2 128-bit encryption (NetVault LiteSpeed for SQL Server)
3DES-168	3DES 168-bit encryption (NetVault LiteSpeed for SQL Server)
RC4-128	RC4 128-bit encryption (NetVault LiteSpeed for SQL Server)
AES-128	AES 128-bit encryption (NetVault LiteSpeed for SQL Server, Red Gate SQL Backup, or Idera SQL safe backup)
AES-192	AES 192-bit encryption (NetVault LiteSpeed for SQL Server)
AES-256	AES 256-bit encryption (NetVault LiteSpeed for SQL Server, Red Gate SQL Backup, or Idera SQL safe backup)

EncryptionKey

Enter the key that is used to encrypt the 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.

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 read/write filegroups of all user databases

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@ReadWriteFileGroups = '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 NetVault LiteSpeed for SQL Server, 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',
@EncryptionType = 'AES-256',
@EncryptionKey = 'MyPassword',
@Throttle = 10

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

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

I. Back up all user databases, using compression, encryption, and Red Gate SQL HyperBac

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@BackupSoftware = 'HYPERBAC',
@Compress = 'Y',
@Encrypt = 'Y'

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',
@EncryptionType = 'AES-256',
@EncryptionKey = '8tPyzp4i1uF/ydAN1DqevdXDeVoryWRL'

Execution

Execute the stored procedures from SQL Server Agent CmdExec jobs, using sqlcmd and the -b option. Pass the parameters to the stored procedures by name:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'FULL'" -b
Use SQL Server Agent output files to ensure that you always have a full set of information, in case of an error.
You can use the MaintenanceSolution.sql script to install the SQL Server Maintenance Solution. Then the sqlcmd commands and the output files will be configured for you.

Tuesday, 15 October 2013

SQL Server Maintenance Solution