Introduction

If you are already using Ola’s maintenance script (hint: you should) to facilitate SQL Server routine maintenance on your private or public cloud (Azure VM/AWS EC2/GCP CE), you are familiar with Ola’s Maintenance. In a nutshell, Ola’s Maintenance Solution allows for a few easy steps to complement SQL Server installation with routine basic maintenance tasks – backups, corruption checks, and index and statistics.

One of Ola’s maintenance scripts is Ola’s Backup maintenance procedure. This procedure allows for just a few clicks to configure the entire backup strategy – full, diff, and log database backups. While that procedure works great, sooner or later, you will want to improve and make it faster.

The following blog assumes that you are using the Ola Hallengren DatabaseBackup procedure, and it is going to offer an easy solution to make it a striped backup, i.e., spreading database backups across multiple files to reduce runtime.

Problem

Unfortunately, Ola’s backup procedure doesn’t calculate how many files are needed to make the backup go faster by leveraging some multi-core parallelism. Fortunately, Ola’s backup procedure has full support for backup striping. We only need to feed that number (Fig. #1).

Fig #1

Solution

The following solution (effectively a wrapper on top of Ola’s DatabaseBackup procedure) will get that number from Dynamic Management View (aka DMV), and feed that into Ola’s DatabaseBackup procedure – the recommended number of files based on the vCPUs number up to 8.

We will also use MinBackupSizeForMultipleFiles to instruct the DatabaseBackup Procedure to split the database backup into multiple files when the backup size is larger than 10GB.

The following code on GitHub will create a DBA database (if needed), a maintenance schema (if needed), and add a wrapper stored procedure that adds a stripped database backup, so you can use it effortlessly.

create DBA database (if needed)
https://github.com/steverezhener/DBA/blob/master/createdb.sql

maintenance schema (if needed)
https://github.com/steverezhener/DBA/blob/master/Schemas/maintenance.sql

wrapper full backup stored procedures
https://github.com/steverezhener/DBA/blob/master/Procedures/maintenance.full_backup.sql

The final result should take you from one single backup file (Fig #2 – red) into multiple database backup files (Fig #2 – green).