#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=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=, 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')

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts