Backup a SQL Database Using SQL Server Management Objects (SMO) and PowerShell

In my next article on my SQL and PowerShell series, I will talk about using the SQL Server Management Objects (SMO) to back up a SQL database. If you recall in my previous article on SQL Databases, you saw how I was able to connect to a database and look at various properties of it. Using the same technique to connect to and access a database, I will then begin the process of building a backup object and assigning a backup device to then backup both the database and the transaction log file (if applicable).

Starting off, lets load the assemblies and connect to the SQL server instance.

#region SQL Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
#endregion SQL Assemblies
 

$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'boe-pc\shire'

The first thing I need to do is locate the backup directory that is defined on the SQL server using the BackupDirectory property.

$backupDir = $sqlServer.BackupDirectory

image

Lets see what is in this folder first.

image

The first thing we are going to backup is the SQL database. I am going to connect to the database that I want to backup first, in this case it is AdventureWorks.

$sqlServer.Databases.Name

image

In order to prepare to backup the SQL database, we need to utilize the Microsoft.SqlServer.Management.Smo.Backup type and create an object that will then be used (with some configuration updates) in our backup.

#Create the backup object that will be used for the database backup
$smoBackup = New-Object "Microsoft.SqlServer.Management.Smo.Backup"

 

What we have now is an object that we can modify to meet our requirements for backing up the database.

image

Because I want to backup the database, the Action property will be a Database. I can find out what type of value the Action property by checking for the available values below:

[enum]::GetNames('Microsoft.SqlServer.Management.Smo.BackupActionType')

image

Now lets configure the the Action property as well as specifying the database to backup.

$smoBackup.Action = 'Database'
$smoBackup.Database = 'AdventureWorks'

Now I am going to add a backup device via the Device property and its method AddDevice().

image

The parameters required are the name of the backup and the type of backup device being used, which in this case is a File.

[enum]::GetNames('Microsoft.SqlServer.Management.Smo.DeviceType')

image

Also, I will specify the backup description so I know what this backup is and when it was taken.

#Need to add a device for the backup
$smoBackup.Devices.AddDevice(("{0}\{1}_{2:yyyyMMdd}.bak" -f $backupDir,$Database.Name,(get-date)),"File")       
$smoBackup.BackupSetDescription = ("Full backup of {0} taken on {1:yyyyMMdd}" -f $Database.Name,(Get-Date))
$smoBackup.BackupSetName = "AdventureWorks DB Full Backup"

The last thing that I want to do is ensure that this is a full backup and not an incremental backup by specifying a boolean value of $False for the Incremental property.

#Boolean value on whether to do Incremental backup or not (Full backup)
$smoBackup.Incremental = $False

Now that we have everything configuration, it is time to perform the full backup of this database.

#region Initiate Database Backup
$smoBackup.SqlBackup($sqlServer)

image

Barring any issues with the backup, you won’t get any return status on the backup. We will need to check the backup directory again to see if it is there.

image

Sure enough, there is the backup! One thing to keep in mind is that if you are doing this against a remote SQL server, the backup will be on the remote server. Just be sure to keep this in mind.

Also, a more condensed version of this can be seen here:

$smoBackup = New-Object "Microsoft.SqlServer.Management.Smo.Backup" -Property @{
    Action = 'Database'
    Database = 'AdventureWorks'
    BackupSetDescription = ("Full backup of {0} taken on {1:yyyyMMdd}" -f $Database.Name,(Get-Date))   
BackupSetName = "AdventureWorks DB Full Backup" 
}
$smoBackup.BackupSetDescription = ("Full backup of {0} taken on {1:yyyyMMdd}" -f $Database.Name,(Get-Date))
$smoBackup.SqlBackup($sqlServer)

We aren’t finished yet! Up next is the transaction log (T-Log) backup. There really is not a lot of differences between the setup for the T-Log backup and what we already did for the database backup.

Being that we have already done this with a database backup, I won’t repeat everything and just get straight to the code.

The only thing that matters with this is whether the database is configure for Full instead of Simple.

#Check if Transaction log can be backed up
If ($Database.RecoveryModel -ne 'simple') {
    #CODE TO BACKUP T-LOG
}

Assuming that the RecoveryModel is Full, we can perform the T-Log backup.

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
$smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup -Property @{
   Action = 'Log'
   BackupSetDescription = ("Full Transaction Log backup of {0} taken on {1:yyyyMMdd}" -f $Database.Name,(Get-Date))
   Database = "AdventureWorks"
   BackupSetName = "AdventureWorks Transaction Log Full Backup"
}
 
$smoBackup.Devices.AddDevice(("{0}\{1}_{2:yyyyMMdd}_tlog.bak" -f $backupDir,$Database.Name,(get-date)),"File")
$smoBackup.SqlBackup($sqlServer)

We can now verify that the T-Log backup is in the same place as the database backup.

image

Perfect! We now have our database and its transaction log backed up to the backup directory of the SQL server. Now you can setup incremental backups or continue with full backups at a scheduled time. Keep in mind that this can be done to all of your databases (with the exception of TempDB as it doesn’t need backed up). Hopefully this gives you another option for performing backups on your SQL environment. Next up will be taking these backups and performing a restore of the database and transaction log backup to your server.

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, SQL and tagged , , , . Bookmark the permalink.

One Response to Backup a SQL Database Using SQL Server Management Objects (SMO) and PowerShell

  1. Thanks for posting this blog “Backup a SQL Database Using SQL Server Management Objects (SMO) and PowerShell”. I bookmarked this page, one of my friend working on IT management. I will forward your link to him for better understand. cheers!!!

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