Menu
- Home
- SQL Server Backup
- SQL Server Integrity Check
- SQL Server Index and Statistics Maintenance
- Downloads
- Frequently Asked Questions
- Version History
- Organizations that Use the Solution
- Sign Up for the Newsletter
- License
- Contact
Awards
Sponsors
SQL Server Backup
DatabaseBackup is the stored procedure in the SQL Server Maintenance Solution for backing up databases. It 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 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 (,) character.
| 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 |
Directory
Specify the backup root directory, which can be a local directory or a network share. 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 a local directory. |
| \\Server1\Backup | Back up to a network share. |
DatabaseBackup creates a directory structure with instance name, database name and backup type under the backup root directory.
BackupType
Specify the type of backup: full, differential, or transaction log.
| Value | Description |
| FULL | Full backup |
| DIFF | Differential backup |
| LOG | Transaction log backup |
DatabaseBackup use 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 use 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 that are backed up and verified. Backup files are only deleted if the backup and verify of the database were successful.
DatabaseBackup has a check that no transaction log backups that are newer than the most recent full or differential backup are deleted. This is to guarantee that you can always do 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 use 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 use 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, 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, 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. This is the default. |
| HYPERBAC | Red Gate SQL HyperBac |
| LITESPEED | Quest 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 use the CHECKSUM option in the SQL Server BACKUP command.
BlockSize
Specify the physical blocksize in bytes.
The BlockSize option in DatabaseBackup use 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 use 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 use the MAXTRANSFERSIZE option in the SQL Server BACKUP command.
NumberOfFiles
Specify the number of backup files. The default is one file and the maximum is 64 files.
CompressionLevel
Set the Quest LiteSpeed for SQL Server, Red Gate SQL Backup, or Idera SQL safe backup compression level.
In Quest LiteSpeed for SQL Server the compression levels 0 to 10 are supported, in Red Gate SQL Backup 0 to 4 and in Idera SQL safe backup 1 to 4.
Description
Enter a description for the backup.
The Description option in DatabaseBackup use the DESCRIPTION option in the SQL Server BACKUP command.
Threads
Specify the Quest 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 Quest 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. This is the default. |
| RC2-40 | RC2 40-bit encryption (Quest LiteSpeed for SQL Server). |
| RC2-56 | RC2 56-bit encryption (Quest LiteSpeed for SQL Server). |
| RC2-112 | RC2 112-bit encryption (Quest LiteSpeed for SQL Server). |
| RC2-128 | RC2 128-bit encryption (Quest LiteSpeed for SQL Server). |
| 3DES-168 | 3DES 168-bit encryption (Quest LiteSpeed for SQL Server). |
| RC4-128 | RC4 128-bit encryption (Quest LiteSpeed for SQL Server). |
| AES-128 | AES 128-bit encryption (Quest LiteSpeed for SQL Server, Red Gate SQL Backup, or Idera SQL safe backup). |
| AES-192 | AES 192-bit encryption (Quest LiteSpeed for SQL Server). |
| AES-256 | AES 256-bit encryption (Quest LiteSpeed for SQL Server, Red Gate SQL Backup, or Idera SQL safe backup). |
EncryptionKey
Enter the key that is used to encrypt the backup.
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, the commands are printed only.
| Value | Description |
| Y | Execute commands. This is the default. |
| N | Only print commands. |
Examples
A. Backing up all user databases, using checksums and compression, and verifying the backup
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@CheckSum = 'Y',
@Compress = 'Y'
B. Backing up all user databases, using compression, encryption, Quest LiteSpeed for SQL Server and limiting the CPU usage to 10%
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@BackupSoftware = 'LITESPEED',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionType = 'AES-256',
@EncryptionKey = 'MyPassword',
@Throttle = 10
C. Backing up all 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'
D. Backing 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'
E. Backing 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 job output files with tokens for jobid, stepid, date, and time. Output files ensure that you always have a full set of information, in case of an error. Tokens ensure that the output files have unique file names.
You can use the MaintenanceSolution.sql script to install the Solution. Then this will be taken care of for you.
Getting Starting
- Download MaintenanceSolution.sql.
- Change this line in the script to point to your backup directory, by replacing “C:\Backup” with the path to your directory:
SET @BackupDirectory = N'C:\Backup' - Execute MaintenanceSolution.sql. This script creates the stored procedures, functions, and jobs that you need.
- Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that these jobs are completing successfully. Verify that the backup files are being created. Check the output files in the error log directory.
- Schedule the jobs.
Upgrading
- Download MaintenanceSolution.sql.
- Change this line in the script so as not to create jobs:
SET @CreateJobs = 'N' - Execute MaintenanceSolution.sql. The script drops the old versions of the stored procedures and functions and creates new versions.
- Start the jobs and verify that they are completing successfully.