::::::::: PowerShell :::::::::
Saturday, July 29, 2006
  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?

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


Photobucket - Video and Image Hosting

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 <<<<>


  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

# 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 =
[switch]$Overwrite = $false,
[switch]$Differential = $false)

trap { break; }

# Load SMO assembly
'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies'
"$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(

if ($Overwrite) {
Write-Warning "Overwriting existing backup set"


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 :
Hi Kim - I don't think this script is working for Powershell RC2; do u have an update?
I am very inspired to read your post about digital marketing, it will be very helpful for all new learner to learn Digital marketing courses in pune.

This idea is mind blowing. I think everyone should know such information like you have described on this post. Thank you for sharing this explanation.Your final conclusion was good.
DOT NET Course Chennai
DOT NET Training Institute in Chennai
Post a Comment

Links to this post:

Create a Link

<< Home
Let's get lazy with PowerShell!

Location: Flushing, NY, United States

Experimenting with a different format of blogs...

10/01/2005 - 11/01/2005 / 11/01/2005 - 12/01/2005 / 12/01/2005 - 01/01/2006 / 01/01/2006 - 02/01/2006 / 02/01/2006 - 03/01/2006 / 03/01/2006 - 04/01/2006 / 04/01/2006 - 05/01/2006 / 05/01/2006 - 06/01/2006 / 06/01/2006 - 07/01/2006 / 07/01/2006 - 08/01/2006 / 08/01/2006 - 09/01/2006 / 10/01/2006 - 11/01/2006 / 11/01/2006 - 12/01/2006 /

Powered by Blogger