Split backup files for faster SQL Backups

September 01, 2017

Situation:

SQL backups for large database are always be a challenging task. Depending upon the available system resources, a typical 5 TB database backup may take 6 - 7 hrs to complete. In addition to this, it is very difficult for DBAs to copy a single large backup file over the network.

Solution:

SQL Server provides a flexibility to split single backup file to multiple files. This would allow SQL server to split the workload by creating multiple threads. The advantages of this is backups will run faster and creates smaller files that can be easily copied across the network. Additionally, in most of the cases, Database administrator takes compressed backups which reduce the backup file size. However, for large database backups, overall backups time would be large as SQL server uses single thread to complete the backup operation. In a critical production environment it is always be a requirement to complete the backups before business starts next day. This would ensure the successful backup for disaster recovery and also reduce the load during production hrs.

Configure SQL Script for split backup:

BACKUP DATABASE [AdventureWorks]
TO DISK = 'C:\SQL Server\Backup\AdventureWorks_1.bak',
DISK = 'C:\SQL Server\Backup\AdventureWorks_2.bak',
DISK = 'C:\SQL Server\Backup\AdventureWorks_3.bak',
DISK = 'C:\SQL Server\Backup\AdventureWorks_4.bak'
WITH STATS = 10

Powershell script for split backup:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

$SQLInstanceName = "."
$SQLBackupFolder = "C:\SQL Server\Backup"

$ts = Get-Date -format yyyy_mm_dd_hhmmss

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstanceName
$db = "AdventureWorks"

#Create backup Object
$bkup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")

#Set property for FULL Database Backup
$bkup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database

#Set backup set name
$bkup.BackupSetName = $db + "_backup_" + $ts

#Set Database name to be backed up
$bkup.Database = $db

#Set Compression property
$bkup.CompressionOption = 1

#Set target media
$bkup.MediaDescription = "Disk"

#Set backup files name
$bkup.Devices.AddDevice($SQLBackupFolder + "\" + $db + "_" + $ts + "_1.bak", "File")
$bkup.Devices.AddDevice($SQLBackupFolder + "\" + $db + "_" + $ts + "_2.bak", "File")
$bkup.Devices.AddDevice($SQLBackupFolder + "\" + $db + "_" + $ts + "_3.bak", "File")
$bkup.Devices.AddDevice($SQLBackupFolder + "\" + $db + "_" + $ts + "_4.bak", "File")

#Catch exceptions
TRY {
$bkup.SqlBackup($srv)
}
CATCH
{
$db + " Database backup failed."
$_.Exception.Message
}

Configure Split backups through SQL Management Studio:

1. Right click on Database -> Tasks -> Back up...