(This post comes from one of our Junior DBAs – @SQLDork)
I’m learning how to use dbatools.
Today’s command: Backup-DbaDatabase
I’m using the 64-bit PowerShell ISE (running as administrator), running PowerShell version 5.1 on Windows 10, with a local SQL 2016 install.
Here’s the code, each snippet adding additional parameters and building off the one before it, mostly, with a bunch of 100% hilarious comments.*
*Comments may or may not be 100% hilarious, on account of hilarity not being a measurable substance.**
**Probably.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test
This one works, yaaaay! All it does is take a full backup of one database. We can do better, probably.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Diff
This takes a differential backup, but calling your diffs .bak is bad™
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff
-FilePath lets us set the extension, but in doing so, we lose timestamps. I wonder if there’s a way to fix that… (Spoiler alert: there totally is)
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff -CompressBackup
Also figured out how to compress backups, but it doesn’t work if you try to take a compressed diff after an uncompressed full.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff -FilePath test3.dff -CompressBackup
This one worked, because science!™ Also backup chains, there’s probably a blog post around here somewhere explaining those, you should go find it and send it to all your friends!
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup -initialize
Using -initialize makes it overwrite the existing copy, nothing too exciting.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016
Bare minimum required to run without errors, takes full backups of every system and user database on the box.
Except for tempdb for some reason, idk why*
* I totally know why
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff ` -FilePath test3_timestamp.dff -CompressBackup -ReplaceInName
Using -ReplaceInName lets us use timestamps with reasonable file extensions
Welcome to people-not-yelling-at-you-because-they-can’t-tell-if-the-important-backup-file-is-a-full-or-a-log town!
Population: Us
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test, model
Use commas to take backups of multiple databases at once.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -confirm
Anything that’s a checkbox in the Command Add-On doesn’t need any fancy parameters, it just works™.
If you’re using the PS ISE. If you’re using notepad, stop it.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full ` -FilePath test3_timestamp.bak -ReplaceInName -buildpath -createfolder ` -path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup'
-Path tells the files where to go
-FilePath sets the name/extension of the file
-BuildPath tells PowerShell to make the path if it doesn’t exist
-CreateFolder sends all the files into nice little subfolders for the humans to look at and go “Hey look, subfolders!”.
And then they go drink coffee and complain about the database being “slow”.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Type full ` -FilePath dbname_timestamp.bak -ReplaceInName -ExcludeDatabase test -buildpath ` -path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\donuts'
-ExcludeDatabase has a very non-intuitive and confusing function: it excludes databases from being backed up.
Useful for the really big ones you only want to backup once a week or something.
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test ` -FilePath dbname_timestamp.bak -ReplaceInName -FileCount 2
This makes multiple striped files, appending numbers automatically, as so:
[filename]-1-of-2.bak
[filename]-2-of-2.bak
[filename]-donuts-of-2.bak
Actually that last one was a joke, but that’s all I got for today, and probably the next month or so.
Follow me (b|t) on twitter, where I also probably won’t say anything for the next month or so.
