Day 2 – Backup Basics

One of the most basic things a DBA needs to do is to take reliable backups. Before you undertake any piece of work you need to have somewhere to roll back to in case it all goes horribly wrong.

The dbatools Backup-DbaDatabase function can really help out here.

While it can be automated with SQL Agent jobs, it’s not it’s real strong point. For that you’re better off sticking with Ola Hallengren’s maintenance solution.

The Basics

At it’s most basic all Backup-DbaDatabase needs is a sql instance:

Backup-DbaDatabase -SqlInstance server1\instance 

This will take a full backup of every database on instance server1\instance into the instance’s default backup location

This assumes that you’re running Windows authentication on your SQL instance. If not, you need to provide the credentials to connect with.

You do this with the SqlCredential parameter like so:

Backup-DbaDatabase -SqlInstance server1\instance -SqlCredential $credential

To create a credential you have 2 options:

Using Get-Credential. This will pop up a box for you to enter the username and password to create the credential:

$credential = Get-Credential

Or you can create one on the command line, though this will leave your password in the command history:

$password = ConvertTo-SecureString 'P@ssw0rd' -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ('username', $password)

Just in case you don’t know what the default backup location for an instance is, you can grab it like so:

Get-DbaDefaultPath -SqlInstance server\instance

If you want to backup a specific database or more then you can use the -Database parameter

Backup-DbaDatabse -SqlInstance server1\instance -Database db1, db4

which will backup db1 and db4. or you can pipe in values from Get-DbaDbDatabase like so:

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$ -like '*test'} | Backup-DbaDatabase

Which will back up every database whose name ends in test on the server\instance. Really hand for when the devs want to deploy something new

Other backup types

Now, it’s not always just about the full backups. Of course we can handle differential and log backups, just a parameter away

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$ -like '*test'} | Backup-DbaDatabase -Type Differential

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$ -like '*test'} | Backup-DbaDatabase -Type Log

There is one other type of backup that is very important to be aware of, the COPYONLY backup. When a SQL Server database is in full recovery mode you want to make sure that you don’t interrupt the backup chain. This can ruin your chances of performing a successful restore.

For instance, if you’re using Differential backups. Each differential backup is the sum of all changes from a specific full backup. If you take a new backup, all subsequent differentials are based on that one. That can be a problem if you’re just grabbing a backup to refresh test or to investigate an issue.

A COPYONLY backup does not break the chain, so it can be removed or deleted once it’s no longer needed without breaking your recovery plan

To take one of these, just use the -CopyOnly switch:

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$ -like '*test'} | Backup-DbaDatabase -CopyOnly

Testing a backup command

Maybe you want to try something out and see what’s going to happen, but you don’t want to actually do the backup?

That’s what the -OutputScriptOnly switch is for. This will go through all the same processing as a full run would, the only difference is that Backup-DbaDatabase will just spit out the T-SQL without running the command.

Running this:

Backup-DbaDatabase @Script:appsplat -Database roles1 -FileCount 3 -OutputScriptOnly

will not perform a backup, but will produce this for you:

BACKUP DATABASE [roles1] TO DISK = N'/var/opt/mssql/data\roles1_202004281714-1-of-3.bak', DISK = N'/var/opt/mssql/data\roles1_202004281714-2-of-3.bak', DISK = N'/var/opt/mssql/data\roles1_202004281714-3-of-3.bak' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 1

We’ll be covering what some of those other options are in a post in a couple of days

Backup-DbaDatabase Output

Backup-DbaDatabase produces a lot of output that can be of interest. Quite a lot of it isn’t shown at first glance so we’ll take a look at what’s available:

$output = Backup-DbaDatabase @Script:appsplat -Database roles1 -FileCount 3

Now, the standard output looks like this:

Which gives you the basic information. But if you then ask for everything in the output you get a lot more information:

That’s just about every stat you might want for your backup, or everything you might want to test for after taking the backup.


Today we’ve looked at the basics of what Backup-DbaDatabase can do, tomorrow we’ll be looking at handling backup files

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