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

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:

[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

[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
[/powershell]

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.

[ps collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
#============================================================
# 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
[/ps]

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.

Advertisements

11 comments

  • Excellent article, thanks

    Like

  • Great. thanks for the helpful code.

    Like

  • Thanks for the posting. How would you do a DBCC CHECKDB first and if passes then backup? I have the backups working via SQL Agent using powershell. They read my SLA metadata and either backup up or if CommVault is doing the backup check to confirm backups are happening.
    This is phase one. Phase two of my project to to test the backups.

    For some reason my brain is not making the jump of capturing an error on the DBCC CHECKDB and then throwing an error so that SQL Agent will notify.

    Thanks

    Like

  • Hi! This is kind of off topic but I need some help from an established blog. Is it difficult to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about making my own but I’m not sure where to start. Do you have any points or suggestions? Many thanks

    Like

  • I came across this. Tried it on SQL Express 2008 R2 – had to reference

    $server = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “(local)SQLExpress”

    and add version 10.0 to

    LoadWithPartialName(“Microsoft.SqlServer.SMO, Version=10”)

    and it worked as expected. Great post!

    Like

  • Doesn’t work for me, even if I added Stan Littlefield’s extras because I’m using SQL Express 2008 R2.

    Came up with:

    Exception calling “SqlBackup” with “1” argument(s): “Backup failed for Server ‘compnameSQLExpress’. ” At .ps1:45 char:21 +$smoBackup.SqlBackup <<<< ($Server)
    +CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    +FullyQualifiedErrorID : DotNewMethodException

    Anyone have any ideas how to make it work?

    Like

  • Hi! I got same problem as Mikko Nurmi. Solution anyone?

    Like

  • Hi, I have the same error as Mikko and Kuba! Anyone find a solution to that yet?

    Like

  • Hi,
    I got it to work finally. It’s verry important to have the correct connection string!

    I’m using SQLEXPRESS on Server Core 2008 R2 so my connection string has to be ‘LOCALHOSTSQLEXPRESS’

    Hope this helps someone else.

    Like

  • Thanks for this!
    There is a typo in the script “(local)” should have been (“local”)

    Cheers

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s