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!
