Handling striped SQL Server backupsets using PowerShell

PowerShellA quick question on twitter from William Durkin (b|t) reminded me that I hadn’t covered SQL Server multistriped backupsets during my 31 Days of SQL Server Backups and Restores with PowerShell series, so without further ado I’m going to correct that:

In case you haven’t met multistriped backupsets before, a quick introduction. SQL Server allows you to backup a database to more than one backup file at a time. So this is a perfectly valid T-SQL statement:

backup database adventureworks2012 to disk='c:\psbackups\adv2012a.bak', disk='c:\psbackups\adv2012b.bak', disk='c:\psbackups\adv2012c.bak'

In fact, SQL Server allows you to specify up to 64 devices to spread you backup across. SQL Server will stripe the backup data equally across all the devices you’ve specified, so you’ll end up with equal size files. But you don’t get to say what data goes into a specific file, and you will need all the files to restore any part of the database (ie; you can’t be sure that adv2012b.bak in our example contains , and even if you could work it out you couldn’t restore it without having adv2012a.bak and adv2012c.bak as well).

So why would you bother? Well, it can help speed up you backups if your bottleneck is writing the backup to disk. By striping your backup across multiple drives, or even across multiple HBAs, you can maximise your throughput. But this is a double edged sword, as SQL Sever is striping the data as it’s written it can only write at the speed of the slowest device!. So if one of your devices is having an off day, then your whole backup is going to run slow. For all that, it can still be a very handy option if you’ve a tight backup window and reliable storage.

As far as PowerShell is concerned, each file is just another backup device. So to perform our example backup we would run:

import-module "SQLPS" -DisableNameChecking

$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("Server1")
$backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$backupname1 = "C:\psbackups\adv2012a.bak"
$backupname2 = "C:\psbackups\adv2012b.bak"
$backupname3 = "C:\psbackups\adv2012c.bak"

$backupdevice1 = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname1,$devicetype)
$backupdevice2 = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname2,$devicetype)
$backupdevice3 = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname3,$devicetype)


$backup.Database = "AdventureWorks2012"
$backup.Devices.add($restoredevice1)
$backup.Devices.add($restoredevice2)
$backup.Devices.add($restoredevice2)

$backup.SQLBackup($sqlsvr)

Or we could build the backup devices dynamically by looping through an array of targets:

$targets=@("\\server2\backups","\\server3\backups","\\server4\backups")
foreach ($target in $targets){
    $backupname = $target+"\"+$backup.Database+".bak"
    $backupdevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname,$devicetype)
    $backups.Devices.Add($backupdevice)
}

And then to restore it, we just pull the devices back in again:

import-module "SQLPS" -DisableNameChecking

$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("Server1")
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$backupname1 = "C:\psbackups\adv2012a.bak"
$backupname2 = "C:\psbackups\adv2012b.bak"
$backupname3 = "C:\psbackups\adv2012c.bak"

$restoredevice1 = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname1,$devicetype)
$restoredevice2 = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname2,$devicetype)
$restoredevice3 = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname3,$devicetype)


$restore.Database = "AdventureWorks2012"
$restore.ReplaceDatabase = $True
$restore.Devices.add($restoredevice1)
$restore.Devices.add($restoredevice2)
$restore.Devices.add($restoredevice2)

$restore.SQLRestore($sqlsvr)

Hopefully that’s provided you with some background to striping backupets across multiple files, and how you can use PowerShell to restore them.

If there are any other SQL Server backup or Restore options you’d like to see me cover using PowerShell then please just drop me a note in the comments, ping me an email or via twitter

Leave a Reply

Your email address will not be published. Required fields are marked *