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

Lets see what is in this folder first.

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

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.

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

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().

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

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)

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.

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.

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.