I don’t know why I am slow to take up new tools, but I am. I recently, like back in December, began working with DBATools. In case you’re not familiar, DBATools is a PowerShell module assembled by the community over at DBATools.io. This module, oh this wonderful module, makes some of the most aggravating parts of a DBA’s job so much easier. Things like server migrations, copying users, adding Ola’s jobs, and a slew of other things so much simpler.
What sold me? Well in December I had to migrate 70 some 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 a good as a time as any to learn Powershell and solve a problem. That thought process got me thinking about DBATools, and I decided to see if the module could help me out. Oh, and help me out it did. This module is a tool EVERY DBA should be using.
DBATools works
I went about setting up the list of databases into a Powershell list, created a specific UNC file location for the backups to land on, and ran the following the code:
1Backup-DbaDatabase -SqlInstance $server -Database $dblist -Path $path -Type Full -CopyOnly -compressbackupWhere $server was the name of the server I was backing up from, $dblist was the list of databases I needed, $path was the UNC location. The -CopyOnly switch, made sure I didn’t affect the backup chain on the server I was migrating from. Although, this was less of a concern post-migration.
The really cool part about this, or at least I thought it was cool, was the restore command.
1Restore-DbaDatabase -SqlInstance $newServer -path $pathThe restore command allowed me to point the restore command to the path, and it restored each database in the directory to the default file locations. Pure genius.
Other areas of the toolset I used were copy users, migrate settings, migrate jobs, and copy linked servers to name a few. Oh, this tool saved me so much work and time. I’m not sure why I did not embrace it earlier.