Backing Up Databases With The dbatools PowerShell Module

We do a lot of database backups and restores at work, outside of our regular backup routines. A lot! I’ve actually done as many as 40 in one day. Our “enterprise” backup system is too cumbersome to try to use like this, so we needed something that would allow us to take ad-hoc backups quickly and easily.  This is where dbatools really helps out. Here are few examples:

Backing Up A Single Database

Backing up a database (or multiple databases) is done by calling the Backup-DbaDatabase command. The documentation is pretty straight forward, and a basic FULL backup command would be:

Now, if you run the above script, there is a chance you would be met with the following errors, like I was:

Backup-DbaDatabase : A parameter cannot be found that matches parameter name ‘Path’.
Backup-DbaDatabase : A parameter cannot be found that matches parameter name ‘FilePath’.

This just means that you are using an earlier version of the dbatools library, where “path” and “filepath” were referred to as “BackupDirectory” and “BackupFileName” instead. This was changed with version 1.0.0 in June of 2019, so you can either upgrade your dbatools version (highly recommended) or use “BackupDirectory” and “BackupFileName” instead of “path” and “filepath” in your command.

Now, let’s make our script a little more versatile by using variables for the parameters. The below command will take a FULL COPY_ONLY backup of “DBname” on “SQLServer01” and place it in the “\fileshare\Backups\” directory.

Backing Up Multiple Databases

Once you have the basic backup command working, dbatools makes it very simple to backup multiple databases using the same command. All you need to do is specify a list of databases, and then remove the –FilePath parameter because it is only accepted for single database backups.

Backing Up All Of The Databases

Backing up all of the databases on a server is even easier. Just omit the -Database parameter and it will back up every database on the specified server.

Creating Directories Dynamically

In the previous examples we just wrote the backups to the root of a file share. But sometimes you’ll want to organize or separate your backups based off of server name or database name or backup type. This is something else that dbatools makes very simple to do dynamically. This is done by using the –ReplaceInName parameter and then specifying keywords in your FilePath and Path strings. Per the Backup-DbaDatabase documentation:

-ReplaceInName
If this switch is set, the following list of strings will be replaced in the FilePath and Path strings:
instancename – will be replaced with the instance Name
servername – will be replaced with the server name
dbname – will be replaced with the database name
timestamp – will be replaced with the timestamp (either the default, or the format provided)
backuptype – will be replaced with Full, Log or Differential as appropriate

So, expanding off of our last example, we can modify the $path variable to include these keywords and then specify the –ReplaceInName parameter. As a safety net, we’ll also want to specify the -BuildPath parameter so that dbatools knows to create any directories that are missing from your path.

This would create the “\\fileshare\Backups\SQLServer01\dbname01\” directory and place the dbname01 backup in there, and then do the same for the other databases in the list.

While these are very basic examples, you should start to get an idea of just how versatile and easy to use the Backup-DbaDatabase command is. I use it on a daily basis! I encourage you to read through the documentation and look at the examples there to find other useful features of this command.