On the first day of Christmas dbatools gave to me: Find-DbaCommand
On the second day of Christmas dbatools gave to me: Get-DbaCmsRegServer
On the third day of Christmas dbatools gave to me: DbaMaxMemory
On the fourth day of Christmas dbatools gave to me: DbaAgentJob
On the fifth day of Christmas dbatools gave to me: DbaLogin
On the sixth day of Christmas dbatools gave to me: DbaTempDbConfig
On the seventh day of Christmas dbatools gave to me: DbaAgentAlert
On the eighth day of Christmas dbatools gave to me: DbaDbCompression
On the ninth day of Christmas dbatools gave to me: Backup-DbaDatabase
We are on the home stretch and I have saved the last third of the series to cover some pretty cool/amazing commands. Not that all 500+ commands are not cool/amazing but these last few are used continuously in my environment and bring automation closer to your finger tips with PowerShell and SQL Server then ever before.
Have you ever been asked to take a database backup before a developer does a deployment? Have you ever been asked to backup a database to restore it to a development or other environment? How about, hey can you take a quick transaction log backup? These can all be accomplished using Backup-DbaDatabase and only changing a couple of parameters each time.
Performs a backup of a specified type of 1 or more databases on a single SQL Server Instance. These backups may be Full, Differential or Transaction log backups.
Lets take a look at backing up all our databases to a network location.
Backup-DbaDatabase -SqlInstance localhost\sql2017 -BackupDirectory '\\lxdw17181\c$\SQLData\Backups'
Add the –Type parameter to get a Full, Diff or Log backup. Or try the –Database parameter to only do a specific database or a comma list of multiple databases.
Advanced Command Usage:
$Databases = Find-DbaDatabase -SqlInstance localhost\sql2017 -Pattern SQL
Backup-DbaDatabase -SqlInstance localhost\sql2017 -Database $Databases.Name -BackupDirectory '\\lxdw17181\c$\SQLData\Backups'
So here, we are mixing the Find-DbaDatabase command to locate the databases we want to backup and then passing the results from a variable to the Backup-DbaDatabase command. This can be helpful your database names are long or you are just lazy like me and the copy and past is to much. However, there are other handy ways to use this combination of commands that might fit your need of the day.