2009-01-06

Differential database backup using SMO

A differential database backup of a single database can be done like this using SMO and PowerShell:

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$SsdbName = 'Sandbox\Ssdb0'
$BackupPath = 'Z:\Backup\Ssdb0'
$Server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $SsdbName
$Database = $Server.Databases['msdb']
$Backup = New-Object 'Microsoft.SqlServer.Management.Smo.Backup'
$Backup.Checksum = $true
$Backup.Database = $Database.Name
$Backup.Incremental = $true
$Backup.BackupSetDescription = 'Differential backup of the database [' + $Database.Name + '].'
$BackupFileName = $Database.Name + '_Diff.bak'
$BackupDevice = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem'
$BackupDevice.DeviceType = 'File'
$BackupDevice.Name = [System.IO.Path]::Combine($BackupPath, $BackupFileName)
$Backup.Devices.Add($BackupDevice)
$Backup.BackupSetName = $Database.Name + ' ' + $BackupType + ' backup'
$Backup.SqlBackup($Server)
$Backup.Wait()
"[{0}] is backed up to the file '{1}'." -f $Database.Name, $BackupDevice.Name


The generated backup file is named "msdb_Diff.bak". A more unique filename is preferred, but this is just a simple example.

The output of the script is:
[msdb] is backed up to the file 'T:\Backup\Ssdb0\msdb_Diff.bak'.

A more detailed description of SMO backup using PowerShell is done by Muthusamy Anantha Kumar in a article at Database Journal (link).

No comments: