Backup SQL with Powershell (long)

Had to set up back up a SQL Express 2005 database.  Why some vendors insist on creating software dependent on SQL Express, and then not giving their clients any way to back up said data, is mind-bogglingly stupid.

Here's a PowerShell script I wrote to accept a server name, database, and target backup folder.  It performs a single full backup when called. 

This script, which includes error-handling, sub-folder creation and more robust error reporting, is a lot more involved than any examples I found online, had to amalgamate the knowledge of dozens of google bing searches. 

Some key syntax features of Powershell that I learned about and implemented here:

1. The InnerException loop is the only way to get the actual SQL Error out.  Otherwise, you're just stuck with Error 3041, "BACKUP failed to complete the command", which is not informative at all.  This is probably the most critical part of this, the ability to get meaningful errors into a text file log in the event of a backup failure.
2. The Test-Path verifies if the container exists, so that you can create a directory if it doesn't.
3. The $SMOBackupObject.Database wants a Database name (string) not a Database Object.  That really threw me off for an hour or so.  My fault, ofcourse.
4. I used the SilentlyContinue ErrorActionPreference so that the user would see a clean error message (in my if $error.count section) instead of a messy red error from PowerShell, which would not be informative anyway (see #1).
5. I used both the Trap syntax (to access the Exception object) and the if $error.count, because I wanted to have it return something to the end user regardless. 
6. The out-null parts also hide messy returns to the end-user.
7. The $error.clear() is also pretty important, as it clears out the errors that had run beforehand, if the user wants to try running the script again.  Not sure why this is necessary, its not intuitive that errors persist like that.  Open to any advice.

function SQLFullBackup ([string]$SQLServerName, [string]$SQLDBname, [string]$BackupFolder) 
{  
 
    # SQL Server Backup
    # Executes a single FULL backup of a given database 
 
    $error.clear()
    $ErrorActionPreference = 'SilentlyContinue'
    $BackupDate = Get-Date -format yyyyMMddHHmmss
 
    # SPECIFY SQLServer\Instance name with this variable
    $SQLServer =  New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServerName
 
 
    Trap [Exception] {
        $err = $_.Exception
        while ( $err.InnerException )
            {
            $err = $err.InnerException
            $errorfullmessage += $err.Message + " "
            };
 
        $errorfullmessage | out-File ($BackupFolder + "\BackupHistory_Error_" + $SQLDBName + "_" `
            + $BackupDate + ".txt");
    }
 
    #Create the subfolder if it does not already exist.
    if ((Test-Path -path ($BackupFolder + "\" + $SQLDBName) -pathtype container) -ne $True)
    {
        New-Item -path $BackupFolder -name $SQLDBName -type directory | out-null
    }
 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [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
 
    $SMOBackupObject = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
    $SMOBackupObject.Database = $SQLDBName
    $SMOBackupObject.Action = "Database" # File, Log
    $SMOBackupObject.Incremental = $false
    $SMOBackupObject.Devices.AddDevice($BackupFolder + "\" + $SQLDBName + "\" + $SQLDBName + "_" `
            + $BackupDate + ".bak", "File")
    $SMOBackupObject.SqlBackup($SQLServer) 
 
    if($error.count -gt 0)
    {   
        "Backup of database " + $SQLDBName+ " failed.  See log file in " + $BackupFolder + "\BackupHistory_Error_" `
        + $SQLDBName + "_" + $BackupDate + ".txt"
    }
            Else
    {
        "Backup of database " + $SQLDBName + " successful."
    }
 
};
 

Here are the function calls, per database, for the above function:

SQLFullBackup ".\SQLEXPRESS" "express" "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup"
SQLFullBackup ".\SQLEXPRESS" "express2" "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup"

Here's sample output of a successful call or a failed call.

PS C:\Windows\system32>C:\powershell_scripts\sqlfullbackup calls.ps1
Backup of database express successful.
Backup of database express2 failed.  See log file in 

c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\BackupHistory_Error_wexpress2_20100810094357.txt 

Here's the files created:
BackupHistory_Error_express2_20100810094357.txt
\express\express_20100810094357.bak

And the error log actually contains an informative SQL Error.  In this case:

An exception occurred while executing a Transact-SQL statement or batch. 

Could not locate entry in sysdatabases for database 'express2'. No entry found with that name. 

Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally. 

How did this DBA first get into Powershell?  Here's a blog post about it.