Day 6 – Backup-DbaDatabase in Scripts

One of the great things with dbatools is that you can pull all of the different commands together to make some really powerful scripts for yourself.

Backup-DbaDatabase is no exception. In today’s post I’m going to show you a couple of things you can do with it, and hopefully inspire you to come up with some ideas for your own usage

Backup-DbaDatabase script ideas

Perhaps you work for an ISV so you’re having new customer databases spun up throughout the day. SQL Server won’t start taking transaction log backups until an initial full backup has been taken. Using Get-DbaDatabase we can soon whip up a script to do that automatically

    Get-DbaDatabase -SqlInstance server1\instance | Where-Object {$_.LastFullBackup -eq '01/01/0001'} |
        Backup-DbaDatabase -Path \\prod\backups -CreateFolder

There we go, we return all databases with Get-DbaDatabase, filter to those with a LastFullBackup ‘done’ on ’01/01/0001′ (which is SQL Sever’s answer for NEVER) and pipe them into Backup-DbaDatabase

If you want to think bigger than just a single instance, dbatools works great with Central Management Servers. So you can check your whole estate like this:

    Get-DbaDatabase -SqlInstance server1\instance | Where-Object {$_.LastFullBackup -eq '01/01/0001'} |
        Backup-DbaDatabase -Path \\prod\backups -CreateFolder

You can use the time filter if you’re about to do something where you want a bit of a safety net, just in case. So you could put this at the top of your script to minimise data loss:

    if ((Get-DbaDatabase -SqlInstance server1\instance).LastFullBackup -lt (Get-Date).AddHours(-1)) {
        $results = Backup-DbaDatabase -Path \\prod\backups -CreateFolder -CopyOnly
    }
    if ($false -in $results.BackupComplete){
        $failedDbs = ($results | Where-Object {$_.BackupComplete -eq $false}).Database -Join ','
        Write-Error "Backup of $faileDbs didn't complete, exiting script"
        $false
        return
    }

We check for any database in the results that didn’t complete, then output a useful error message before exiting the script returning $false so we can act on it if needed.

So, if you’re more than a hour since the last full backup it’ll take a CopyOnly full backup for you.

Or, let’s say you want to take a log backup of all databases in full recovery mode:

    Get-DbaDatabase -SqlInstance server1\instance -ExcludeAllSystemDb | Where-Object {$_.RecoverModel -eq 'Full'}
        Backup-DbaDatabase -Path \\prod\backups -CreateFolder -Type Log
    }

We were talking about backup performance tuning yesterday, and said that striping was a great way of improving throughput. Sometimes though it can make small database backups slower, so maybe skip the striping when the databae is less than 20mb say:

    Get-DbaDatabase -SqlInstance server1\instance -ExcludeAllSystemDb {
        $fileCount = 1
        If ($_.SizeMb -ge 20){
            $FileCount = 3
        }
        Backup-DbaDatabase -Path \\prod\backups -CreateFolder -Type Full -FileCount $fileCount
    }

Exploring Backup-DbaDatabase Parameters

And on the performance tuning, perhaps you want to try tuning your backups? Again, this is NOT something to do on production! You’ll annoy someone. Lets try finding the sweet spot for FileCount:

Now we said that MaxTransferSize can be any value up to 4MB in 64kB chunks, so we’ll step from 64kB up to 4MB and see where we hit diminishing returns. To avoid storage issues we’ll dump the backup to NUL:, and we’ll do it as CopyOnly to not break any backup chains

    $fileCount = 1
    $results = @()
    while ($fileCount -le 8){
        $backupOutput = Backup-DbaDatabase -SqlInstance localhost -Database prod -FileCount $fileCount -CopyOnly -Path c:\test\ -CompressBackup
        $results += [PSCustomObject]@{
                        FileCount       = $FileCount
                        Duration        = $backupOutput.Duration
                    }
        Remove-Item c:\test\*.bak
        $fileCount++
    }
    $results | Sort-Object Duration

On my test VM that gives me the following results

FileCount Duration
--------- --------
        8 00:00:26
        6 00:01:09
        7 00:01:28
        4 00:01:49
        2 00:01:55
        5 00:01:56
        3 00:02:09
        1 00:03:18

Now, there’s a bit of jumping around with those times. And that’s because I was running some other loads on the same host at the time, but it just goes to show that you need to run this sort of testing multiple times and at different times of the day just to make sure you get reliable results.

You could a similar pattern to tune any of the other Backup-DbaDatabase parameters

Conclusion

Hopefully these examples have given you some idea of how you can use Backup-DbaDatabase in your own scripts. The last example shows how it can be used to tune backup parameters. Even if you’re not using Backup-DbaDatabase to do your scheduled backups you can transfer the settings across to whicheve solution your working.

If you’re interested in really building up the scripting options the the ‘dbatools in a month of lunches’ book will give you lots of other ideas on how to proceed.

All posts in this series can be found at 31 Days of dbatools Backup and Restores