Powershell Add-Member: IsLogBackupAllowed ?

In my previous post, I shared some of the issues I had when attempting to get Add-Member to work with the SMO Database object. In this post, I’ll share some of the fruits of that experience as I show how to quickly determine if a database can have its log backed up through Powershell and SMO.

That’s Easy – just check the recovery model.

I could just say – yup – blog post done. However, it’s not. Consider the following code:

$instancename = ".\SQL2008R2"
$dbname = "TestFullRecovery"

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

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")
$Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup

$db = $srv.Databases["$dbname"]

if($db.RecoveryModel -eq "Full")
{
    #Take log backup of database only if the database is in full recovery        
    $Backup.Database = $db.name
    $Backup.Action = "Log"
    $Backup.Devices.AddDevice("C:\BAK\TestFullRecovery_log.trn", "File")
    try{$Backup.SqlBackup($srv)}
    catch
    {
        throw $_.Exception.GetBaseException()
    }
}

If you run this on a database that is in Full recovery mode but has not had a full backup, the following error will be thrown:

BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.

This is because the database is in what is known as “pseudo-SIMPLE” mode. Paul Randal describes this issue here as he offers a T-SQL function to determine whether or not a database is really in Full Recovery mode. This can happen if a new database is created and not backed up yet or if the database has recently been switched into Full recovery mode without a subsequent full backup. If you are running in an environment where the application generates new databases like mushrooms, this can become an important issue to resolve.

One option if you are using SMO and Powershell for backups is that you could just catch the error and ignore if the error message is identical to that described above. I do not generally like relying on this type of exception handling – why knowingly execute code that will fail. Let's look at a more proactive solution.

Adding the IsLogBackupAllowed Property

Using Add-Member, we can add a new boolean property to the database object. In this case - instead of adding it as a literal value with the NoteProperty MemberType, the ScriptProperty MemberType allows us to create a property that can be dynamic at run time. Using the special variable "$this" within the construction of the property allows the value to be determined based upon other properties that already exist on the object as well as executing methods of the object to which this new property belongs.

I decided to use the name of “IsLogBackupAllowed” for this new property. This name seemed to fit in well with the other boolean properties of the database object like IsDatabaseSnapshot or IsMirroringEnabled. Leveraging the –PassThru parameter of Add-Member, we can create a simple function that will bring the functionality that Paul Randal created in T-SQL into Powershell. When referencing this function - it will return back the object you put into it with the addition of the IsLogBackupAllowed property.

$instancename = ".\SQL2008R2"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")

function Add-DBMembers 
{
    param(
        [Microsoft.SqlServer.Management.Smo.Database]$Database
    )
    
    $db | Add-Member -Name IsLogBackupAllowed -MemberType ScriptProperty `
        -Value {      
      if($this.Parent.Version.Major -ge 9)
      {
            if(([Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full,
                [Microsoft.SqlServer.Management.Smo.RecoveryModel]::BulkLogged)`
                  -contains $this.RecoveryModel)
            {
            if($this.ExecuteWithResults(
                  [string]::Format("SELECT last_log_backup_lsn 
                          FROM sys.database_recovery_status
                        WHERE database_id = {0}", $this.ID)
                        ).Tables[0].Rows[0].last_log_backup_lsn.GetType() -ne `
                            [System.DBNull])
                  {
                        $true
                  }else{$false}
            }else{$false}
      }else{$null}
    } -PassThru
    
}
$db = $srv.Databases['TestFullRecovery']

$db = Add-DBMembers -Database $db

$db.IsLogBackupAllowed

The function adds a ScriptProperty of IsLogBackupAllowed to a database object that is provided as a parameter. To determine whether the transaction log backup is allowed, the script first determines if the RecoveryModel property of the database is either Full or BulkLogged. If the database is in Simple recovery mode, there is no chance of getting a log backup. Once it is determined that the database could potentially have a log backup taken, the ExecuteWithResults method is implemented to use the same select from Paul’s post to determine the ability of backing up the log. If anything other than “DBNull” is returned for the column status, a value of True is sent to the caller. If the instance is not SQL 2005 or higher, this property returns $null . SQL 2000 does not make this metadata readily available.

But wait – there’s more!

If you’re asking yourself, That’s fine and dandy, but all the work I’m doing in the Add-Member could be done inline when taking log backups.  What's with all the additional work of making it a property? I'll return your question with a question.

What if the Add-Member step was not necessary? What if you could just reference $db.IsLogBackupAllowed in your code? In the next post, I’ll be showing you how this can be accomplished using the Types.ps1xml files as well as showing how to extend various SMO objects with properties that I have found to be handy in routine maintenance tasks.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter