SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell

Written by belle
Posted May 31, 2009 at 10:04 am

PowerShell makes it easier to manage even your database backups and restore.

To do a SQL Server backup in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:

Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.

Other assemblies you may want to load are:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

Also another point to note is the types of backup you can do. BackupActionType specifies the type of backup. Valid values for this option are Database, Files, Log

Here’s the script. This script is for one specific database. If you want to use this for several database, you will just need to use this code inside a loop.
Better yet, put this in a function, and call this in a loop. I will try to do that sometime soon.

#============================================================
# Backup a Database using PowerShell and SQL Server SMO
# Script below creates a full backup
# Donabel Santos
#============================================================
#specify database to backup
#ideally this will be an argument you pass in when you run 
#this script, but let's simplify for now
$dbToBackup = "test"
#clear screen
cls
#load assemblies
#note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008
#otherwise may get this error
#Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure
#the assembly containing this type is loaded.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
#create a new server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDirectory = $server.Settings.BackupDirectory
#display default backup directory
"Default Backup Directory: " + $backupDirectory
$db = $server.Databases[$dbToBackup]
$dbName = $db.Name
$timestamp = Get-Date -format yyyyMMddHHmmss
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
#BackupActionType specifies the type of backup. 
#Options are Database, Files, Log
#This belongs in Microsoft.SqlServer.SmoExtended assembly
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($backupDirectory + "" + $dbName + "_" + $timestamp + ".bak", "File")
$smoBackup.SqlBackup($server)
#let's confirm, let's list list all backup files
$directory = Get-ChildItem $backupDirectory
#list only files that end in .bak, assuming this is your convention for all backup files
$backupFilesList = $directory | where {$_.extension -eq ".bak"}
$backupFilesList | Format-Table Name, LastWriteTime

In the next post, I will show you how to take your backup file and restore to an existing database of the same name, and also to restore to a database with a different name.

Rating: 9.5/10 (35 votes cast)

Rating: +9 (from 9 votes)

SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell, 9.5 out of 10 based on 35 ratings

Be Sociable, Share!