Backup Databases with PowerShell and dbatools

Keeping on the recent PowerShell trend, let’s use PowerShell to accomplish a primary task of any database administrator: backups. With PowerShell and dbatools, you can do a simple backup or add a range of options to fit your needs.

Starter Backup

Let’s start out with taking a basic database backup using Backup-DBADatabase from dbatools. We’ll include our instance, the backup type, the database, the backup directory, and our backup name. For this example, we’ll take a backup of the ScriptHQ database on CALLIHAN\MSSQLSERVER02.

Backup-DBADatabase -SqlInstance CALLIHAN\MSSQLSERVER02 `
    -Type Full -Database ScriptHQ `
    -BackupDirectory C:\Users\Chad\Desktop\PS_Backup `
    -BackupFileName ScriptHQ.bak

Running this script will put our ScriptHQ.bak backup in C:\Users\Chad\Desktop\PS_Backup.

Using -ReplaceInName

What if we need to run the script for multiple instances and databases? We prefer not to be bothered with updating the file name in duplicates of the script. Instead, we can add the -ReplaceInName switch to our script and alter it to use the servername and dbname keywords. When the script runs, servername and dbname will be replaced by the actual server name and database name.

Backup-DBADatabase -SqlInstance CALLIHAN\MSSQLSERVER02 `
    -Type Full -Database ScriptHQ `
    -BackupDirectory C:\Users\Chad\Desktop\PS_Backup `
    -BackupFileName servername_dbname.bak `
    -ReplaceInName

We can also add the backup time so we can see from the filename when the backup was taken:

Backup-DBADatabase -SqlInstance CALLIHAN\MSSQLSERVER02 `
    -Type Full -Database ScriptHQ `
    -BackupDirectory C:\Users\Chad\Desktop\PS_Backup `
    -BackupFileName servername_dbname_timestamp.bak `
    -ReplaceInName

The backup will show up looking like this:

Striped Backups with -FileCount

Do you need striped backups? PowerShell can handle that. We’ll add -FileCount 4 to get four backup files:

Backup-DBADatabase -SqlInstance CALLIHAN\MSSQLSERVER02 `
    -Type Full -Database ScriptHQ `
    -BackupDirectory C:\Users\Chad\Desktop\PS_Backup `
    -BackupFileName servername_dbname_timestamp.bak `
    -ReplaceInName `
    -FileCount 4

More Where That Came From

These examples are just a taste of what’s available with Backup-DbaDatabase. Check out the Backup-DbaDatabase documentation for more info. Backups are no good if you can’t restore them so it’s also worth your time to check out the documentation for Restore-DbaDatabase.

Thanks for reading!