When should I stripe SQL Server database backups

What is a striped backup?

First of all a definition of what we mean when we say stripe SQL Server database backups. When we talk about striping a backup, it simply means the overall backup is distributed across several files, often on different drives. Microsoft’s description.

What are the benefits?

1. Time
   1. Writing to multiple files can save time. This is because the files can be written to the file system in parallel. This is useful if your backups are taking too long and you would like/need them to complete faster.
2. Storage
   1. If a backup drive is getting full, you can split your backup across multiple drives thus saving space on each drive. The total space used will be similar to keeping the backup on a single file but you may see a bit more overall space taken up.

Striped vs Single File

It’s a balancing act. My advice is stick with the defaults until you have an issue or would like something to perform better. Then it’s a good time to experiment and see how you get on. As a rule, the bigger your backups become, the more useful you may find striping your database backups.

How to stripe my backups?

We will discuss T-SQL and Powershell. For Powershell, I’m using the DBATools PowerShell module. DBATools covers far more scenarios than Microsoft’s own default module for SQL Server and is being used and tested all day every day. If you wish to run the powershell commands below, you will need to have dbatools installed. For the T-SQL commands, these all run natively in SSMS.

T-SQL

-- Split file across two drives as not enough space on E:
BACKUP DATABASE StackOverFlow2010
TO DISK = N'E:\Backup\StackOverFlow2010\StackOverFlow2010_1.bak',
DISK = N'F:\Backup\StackOverFlow2010\StackOverFlow2010_2.bak'
WITH COMPRESSION;

T-SQL (Using Ola Hallengren’s DatabaseBackup procedure)

EXECUTE dbo.DatabaseBackup
@Databases = 'StackOverFlow2010',
@Directory = 'E:\Backup\StackOverFlow2010\,F:\Backup\StackOverFlow2010\',
@BackupType = 'FULL',
@Compress = 'Y',
@NumberOfFiles = 2

Powershell (DBATools)

Backup-DbaDatabase -SqlInstance localhost -Database StackOverflow2010 -Path E:\Backup\StackOverflow2010\,F:\Backup\StackOverflow2010\ -CompressBackup

Restoring striped backups

It is therefore worth testing your backups and restore scripts every so often. A backup that cannot be restored is not really a backup.

T-SQL

RESTORE DATABASE StackOverFlow2010
FROM DISK = N'E:\Backup\StackOverFlow2010\StackOverFlow2010_1.bak',
DISK = N'F:\Backup\StackOverFlow2010\StackOverFlow2010_2.bak'

Powershell (DBATools)

Restore-DbaDatabase -SqlInstance localhost -DatabaseName StackOverflow2010 -Path E:\Backup\StackOverflow2010\,F:\Backup\StackOverflow2010\ -WithReplace