Backup Using Ola hallengren Scripts

Ola Hallengren is a Swedish software developer known for creating the “Maintenance Solution” scripts for Microsoft SQL Server. These scripts provide a comprehensive set of maintenance tasks such as backups, integrity checks, and index optimizations for SQL Server databases. They are widely used by database administrators to automate and streamline routine maintenance operations. Ola Hallengren’s scripts are highly regarded in the SQL Server community for their effectiveness and ease of use.

Ola Hallengren’s scripts for SQL Server backup provide a powerful and flexible solution for database administrators. These scripts are designed to automate the backup process and offer various features and options. Here’s a description of how they work:

1. Flexibility: The scripts provide flexibility in defining backup types, including full, differential, and transaction log backups. You can choose the specific databases, filegroups, or even individual tables to back up, allowing for granular control over the backup process.

2. Scheduling: The scripts support scheduling backups using SQL Server Agent Jobs. You can set up regular backup jobs to run at specific intervals, ensuring that your databases are backed up consistently.

3. Integrity Checks: The backup scripts include the ability to perform integrity checks on the backed-up databases. This feature helps ensure that the backup files are free from corruption and can be restored successfully.

4. Verification and Cleanup: After the backups are completed, the scripts offer the option to verify the backup files’ integrity. Additionally, you can specify retention periods to automatically remove older backup files, helping manage disk space usage.

5. Logging and Error Handling: Ola Hallengren’s scripts provide detailed logging and error handling capabilities. They generate comprehensive output files that capture information about the backup process, including success, failure, and any errors encountered during the backup.

Overall, these scripts simplify and automate the backup process for SQL Server databases, making it easier for administrators to ensure data protection and disaster recovery readiness. They are widely used and highly recommended in the SQL Server community due to their reliability and flexibility.

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.

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.

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

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'

Execution

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

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.