#PSTip Backing up a SQL database using SMO

Note: This tip requires PowerShell 2.0 or above.

In today’s tip, we shall see how we can use SQL Management Objects (SMO) in PowerShell to perform a SQL database backup. The Microsoft.SqlServer.Management.Smo.Backup class can be used to achieve this.

Let us see how:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName

Once we have the server object, we can perform backup by running the following code snippet.

$backup = New-Object Microsoft.SqlServer.Management.Smo.Backup -Property @{
   Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
   BackupSetDescription = "Full backup of MyDB"
   BackupSetName = "MyDB backup set"
   Database = "MyDB"
   MediaDescription = "Disk"
}
$backup.Devices.AddDevice("C:\Backup\MyDB.bak", 'File')
$backup.SqlBackup($server)