As the PowerShell backups are just calling the standard T-SQL backup commands, all the usual backup rules a SQL Server DBA is used to still apply.

The main one of these, is that before a Transaction or Differential Backup can be taken the database must have had at least one full backup. This ensures that there is a start to the backup chain.

A common setup at many ISVs is for a new database to be created for each new customer that sign’s up. If that’s an automated process kicked off by an online payment, then the new database could appear at any time, and if you’re only taking full backups once a day the database may not get a full backup for nearly 24 hours! If you’re lucky your developers or suppliers wrote in a check to do an initial backups, do ya feel lucky?

And it’s usually in that first 24 hours that the new customer will be very keen, loading up lots of data and making lots of configuration choicses; but not used to the product, potentially leading to an unexpected deletion of data. If that happens, you’l have no way to recover the data.

By using the following PowerShell script to run your transaction log backups, it will catch any database without a previous backup.

Import-Module "SQLPS" -DisableNameChecking

$Server = "Server1"
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($Server)
$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database

foreach ($Db in $SQLSvr.Databases | Where-Object {$_.Name -ne "tempdb" -and $_.RecoveryModel -eq "Full"}){
    $Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    if ($Db.LastBackupDate -lt $Db.CreateDate){
        $Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
        $BackupName = "c:\psbackups\"+$db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
        $Backup.BackupSetDescription = "Full Back of "+$db.Name
    }else{
        $Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log
        $BackupName = "c:\psbackups\"+$db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".trn"
        $Backup.BackupSetDescription = "Log Back of "+$Db.Name
        $Backup.LogTruncation = [Microsoft.SqlServer.Management.Smo.BackupTruncateLogType]::Truncate
    }

    $DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
    $Backup.Database = $Db.Name

    $BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupName,$DeviceType)
    $Backup.Devices.Add($BackupDevice)
    $Backup.SqlBackup($sqlsvr)
    $Backup.Devices.Remove($BackupDevice
}

We make use of the fact that if a new database doesn’t have a backup then SQL Server returns LastBackupDate as “Monday, January 01, 0001 12:00:00 AM”. We don’t match for that value as if a database has previously existed with the same name and someone hasn’t run sp_delete_database_backuphistory to clear it’s backup history then SQL Server will return the old databases last backup date. But that will still be less than the CreateDate of the new database, so we use that instead.

Tomorrow we’ll be looking at FileGroup and File backups

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.