Back up SQL Server database with SQL Server Objects(SMO) using PowerShell

It is easy to set up SQL Server Agent jobs to backup database in timely fashion.
But why not in PowerShell while SMO is exposed in .NET assembly?

Usage:
Backup-Database -dbName [string] -deviceNames [string[]] [-deviceType [Microsoft.SqlServer.Management.Smo.DeviceType]] [-Overwrite [Switch]] [[-Differential] [Switch]]

Parameters:

• -dbName: Database name to back up e.g.) pubs, AdventureWorks, NorthWind
• -deviceNames: tape back up device name or File system name e.g.) c:/bak/pubs.bak, c:/bak/pubs_diff.bak
• -deviceType: One of "LogicalDevice, Tape, File, Pipe, VirtualDevice"
• -Overwrite: overwite existing backup set
• -Differential: create a differential backup
  

Examples:


There are times that you do not know all the device types available,
then simply enter invalid device type
A great feature of PowerShell is that PowerShell will enumerate DeviceType
and kindly give you list of valid device type names when invalid type is entered

[^_^]PS[597]>Backup-Database -dbName:pubs -deviceNames:'d:\bak\pubs.bak' -deviceType:DontKnow
Backup-Database : Cannot convert value "DontKnow" to type "Microsoft.SqlServer.Management.Smo.DeviceType" due to invali
d enumeration values. The possible enumeration values are "LogicalDevice, Tape, File, Pipe, VirtualDevice".
At line:1 char:73
+ Backup-Database -dbName:pubs -deviceNames:'d:\bak\pubs.bak' -deviceType:D <<<<>

Prerequisites:

1. 
   Microsoft SQL Server 2005 Management Objects Collection: You need to download and install one of SQLServer2005_XMO_.msi versions
2. How to create $server variable: Refer to Scripting Database Objects using SMO (Updated) on SqlTeam.com

Source:

# author: Sung Kim
# Description: Create Full or Differential backups for 
#   SQL Server database with SQL Server Objects(SMO) using PowerShell
function Backup-Database {
  param([string]$dbName = $(throw 'Enter Database name to backup'), 
       [string[]]$deviceNames = $(throw 'Enter device name(s)'),
       [Microsoft.SqlServer.Management.Smo.DeviceType]$deviceType = 
         [Microsoft.SqlServer.Management.Smo.DeviceType]File,
       [Microsoft.SqlServer.Management.Smo.Server]$server,
       [switch]$Overwrite = $false,
       [switch]$Differential = $false)
  
  trap { break; }
  
  # Load SMO assembly
  $PRIVATE:SmoDir = 
    'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies'
  [System.Reflection.Assembly]::LoadFile(
    "$PRIVATE:SmoDir\Microsoft.SqlServer.Smo.dll") | out-null
  
  # Initialize backup object
  $PRIVATE:bkp = New-Object Microsoft.SqlServer.Management.Smo.Backup
  $PRIVATE:bkp.Database = $dbName
  $PRIVATE:bkp.Initialize = $Overwrite
  $PRIVATE:bkp.Incremental = $Differential
  
  # Add backup devices to backup database to
  foreach ($PRIVATE:deviceName in $deviceNames) {
    $PRIVATE:bkpItem = 
      new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem(
        $PRIVATE:deviceName, 
        [Microsoft.SqlServer.Management.Smo.DeviceType]$deviceType)
    $PRIVATE:bkp.Devices.Add($PRIVATE:bkpItem)
  }

  if ($Overwrite) {
    Write-Warning "Overwriting existing backup set"
  }
  
  $PRIVATE:bkp.SqlBackup($server)
  
  if ($Differential) {
    Write-Host -NoNewLine -F Blue "Differential Backup: "
  }
  Write-Host -NoNewLine "$($dbName) has been backed up to "
  Write-Host -ForegroundColor Cyan -NoNewLine "$($deviceNames)" 
  Write-Host " successfully"
}

Tags : Monad msh PowerShell SMO