SQL Server Sleuth

We have a certain customer that has an incredible fondness for undermining the restorability of their databases. Almost all of their databases are in the full recovery model and all-too-frequently the customer will start backing up transaction logs with the dreaded NO_LOG option, which destroys the transaction log backup chain:

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

In a future blog post, I will go into why users resort to this bad practice and what they should be doing instead.

This solution is based on a great blog post from Kalen Delaney (blog, Twitter), that showed how the [sys].[database_recovery_status] catalog view can reveal databases suffering from this problem, using a query like so:

SELECT DB_Name([database_id]) AS [DatabaseName]
FROM [sys].[database_recovery_status]
WHERE (
    ([last_log_backup_lsn] IS NULL)
    AND
    (Cast(DatabasePropertyEx(DB_Name([database_id]), 'Recovery') AS NVarChar(16)) = N'FULL')
    AND
    (Cast(DatabasePropertyEx(DB_Name([database_id]), 'Status') AS NVarChar(16)) = N'ONLINE')
)
ORDER BY [DatabaseName] ASC;

The following PowerShell function uses the query to retrieve a list of problematic databases and then automatically backs up each one:

Function Repair-LogBackupChain (
    [String]$ServerName = $(Throw (New-Object -TypeName 'System.ArgumentNullException' -ArgumentList '$ServerName')),
    [String]$BackupDirectoryPath = $(Throw (New-Object -TypeName 'System.ArgumentNullException' -ArgumentList '$BackupDirectoryPath')),
    [String]$OutputDirectoryPath = $(Throw (New-Object -TypeName 'System.ArgumentNullException' -ArgumentList '$OutputDirectoryPath'))
) {
    [Int]$BackedUpDatabaseCount = 0;
    [String]$BackupFilePath = '';
    [String]$DatabaseName = '';
    [String]$ErrorMessage = '';
    [String]$OutputFilePath = '';
    [String]$Query = "SET NOCOUNT ON; SELECT DB_Name([database_id]) AS [DatabaseName] FROM [sys].[database_recovery_status] WHERE (([last_log_backup_lsn] IS NULL) AND (Cast(DatabasePropertyEx(DB_Name([database_id]), 'Recovery') AS NVarChar(16)) = N'FULL') AND (Cast(DatabasePropertyEx(DB_Name([database_id]), 'Status') AS NVarChar(16)) = N'ONLINE')) ORDER BY [DatabaseName] ASC;";
    [String]$TSQLCode = '';

    #   Create the necessary directories, if they don't already exist.
    $BackupDirectoryPath = Add-Directory -Path $BackupDirectoryPath;
    $OutputDirectoryPath = Add-Directory -Path $OutputDirectoryPath;

    #   Lookup the names of the databases, in the full recovery model, that
    #   lack an intact transaction log backup chain.
    [String[]]$DatabaseNames = @(& 'SQLCmd.exe' -E -S $ServerName -d 'tempdb' -Q $Query -b -h-1 -W);
    $DatabaseNames | Write-Debug;

    #   Verify that the list of databases was successfully retrieved.
    If ((Get-Item -Path Variable:'LastExitCode').Value -eq 0) {
        #   Iterate through the databases that need backups.
        $DatabaseNames | ForEach-Object {
            $DatabaseName = $_;
            Write-Host "Backing up the [$($DatabaseName)] database..." -ForegroundColor 'Cyan';

            $OutputFilePath = "$($OutputDirectoryPath)$(Get-EscapedFileNameComponent -FileNameComponent $ServerName),Backup database,$(Get-EscapedFileNameComponent -FileNameComponent $DatabaseName),$([DateTime]::Now.ToString('yyyyMMddTHHmmssfff')).out";
            Write-Debug "`$OutputFilePath = `"$($OutputFilePath)`"";

            #   Construct the name of the backup file.
            $BackupFilePath = [String]::Format(
                '{0}{1}_{2}_{3}.bak',
                $BackupDirectoryPath,
                (Get-EscapedFileNameComponent -FileNameComponent $ServerName),
                (Get-EscapedFileNameComponent -FileNameComponent $DatabaseName),
                ([DateTime]::Now.ToString('yyyyMMddTHHmmssfff'))
            );

            #   Generate the T-SQL for backing up the database.
            $TSQLCode = [String]::Format(
                "BACKUP DATABASE [{0}] TO DISK = N'{1}';",
                $DatabaseName,
                $BackupFilePath
            );
            Write-Debug "`$TSQLCode = `"$($TSQLCode)`"";

            #   Backup the database.
            & 'SQLCmd.exe' -E -S $ServerName -d 'tempdb' -Q $TSQLCode -o $OutputFilePath -b;

            If (Test-Path -LiteralPath $BackupFilePath -PathType 'Leaf') {
                $BackedUpDatabaseCount++;
            }
            Else {
                $ErrorMessage = "There was a problem backing up the [$($DatabaseName)] database.";
                Throw (New-Object -TypeName 'System.InvalidOperationException' -ArgumentList $ErrorMessage);
            }
        }
    }
    Else {
        $ErrorMessage = [String]::Format(
            "There was a problem querying the [sys].[database_recovery_status] catalog view:`r`n{0}",
            (
                [String]::Join(
                    "`r`n",
                    $DatabaseNames
                )
            )
        );
        Throw (New-Object -TypeName 'System.InvalidOperationException' -ArgumentList $ErrorMessage);
    }

    Write-Host "A total of $($BackedUpDatabaseCount) database(s) was/were backed up." -ForegroundColor 'Green';

    Return;
}

Here’s an example of using the function:

Repair-LogBackupChain -ServerName 'DatabaseServer01\InstanceA' -BackupDirectoryPath 'C:\Temp\' -OutputDirectoryPath 'C:\Temp\';

Note: The code snippet is dependent on two functions that are included in the download for my PowerShell cookbook presentation, Add-Directory and Get-EscapedFileNameComponent.

Maybe by the time that I write the follow-up blog post, I will have received permission to break the customer of this nasty habit (using a tip from Paul Randal (blog, Twitter)).

Hopefully this PowerShell snippet will save you as much time as it has for me!