I don’t know why I’m slow to take up new tools, but I am. Back in December, I finally started working with dbatools. In case you’re not familiar, dbatools is a PowerShell module assembled by the community over at dbatools.io. This module makes some of the most aggravating parts of a DBA’s job dramatically simpler — server migrations, copying users, adding Ola’s jobs, and a lot more.
What Finally Sold Me
In December I had to migrate 70-something small databases off to a new SQL AG. I knew I was going to have to script out the backups, so I decided this was as good a time as any to learn PowerShell properly and solve a real problem. That thought process led me to dbatools, and I decided to see if the module could help. Oh, it helped.
dbatools Works
I set up the list of databases in a PowerShell array, created a UNC file location for the backups, and ran the following:
Backup-DbaDatabase -SqlInstance $server -Database $dblist -Path $path -Type Full -CopyOnly -CompressBackupWhere $server was the source instance, $dblist was the array of databases, and $path was the UNC location. The -CopyOnly switch ensured I didn’t disrupt the backup chain on the source server — important when you’re migrating from a live instance.
The restore command was even simpler:
Restore-DbaDatabase -SqlInstance $newServer -Path $pathPoint it at the path and it restores every database in the directory to the default file locations. No loop, no manual naming — it figures it out.
Other dbatools commands I used in the same migration:
Copy-DbaLogin— copied logins without manually scripting them outCopy-DbaAgentJob— moved SQL Agent jobs across instancesCopy-DbaLinkedServer— replicated linked server definitionsCopy-DbaSpConfigure— migrated instance-level configuration
The amount of manual scripting this replaced was significant. I’m not sure why I didn’t embrace it earlier.
Key Takeaways
- dbatools is a community-built PowerShell module that covers the most tedious parts of DBA work — migrations, copying objects, managing jobs, and more.
Backup-DbaDatabasewith-CopyOnlyis the correct approach for migrations — it takes a full backup without breaking the existing backup chain.Restore-DbaDatabasepointed at a path will restore all databases in that directory automatically.- If you’re doing any SQL Server migration work and aren’t using dbatools, you’re doing it the hard way.