To get started, first download the dbatools module. Four different download/install options are available, including installing from the PowerShell Gallery and github. PowerShell v3+ and SQL Server Management studio required (SSMS provides the libraries we need).
Next, you can use dbatools for a number of scenarios. Our goal with every command is to support:
- SQL Server 2000 – vNext
- Express – Datacenter Edition
- Clustered and stand-alone instances
- Windows and SQL authentication
- Default and named instances
- Multiple instances on one server
- Auto-populated parameters for command-line completion (think -Databases and -Logins)
To migrate all databases, use -AllDatabases. Copy-SqlLogin and other commands migrate all objects by default; We just wanted to be a bit more cautious with Copy-SqlDatabase, especially for those using the detach/attach method.
Visualizing the Available Commands
After coding the three initial commands, Copy-SqlDatabase, Copy-SqlLogin and Copy-SqlJobServer, we actually used SSMS as a guide to help figure out what could be migrated next. Let’s visualize this module with the help of SQL Server Management Studio. Here’s what we’ve got so far.
Hopefully that is comfortable for DBAs who are unfamiliar with PowerShell. It’s really just a bunch of commands that line up with things that what we do, like copying and syncing.
To get started, click links for the commands on the right for descriptions and examples. If you have any questions, you can email [email protected], Tweet @psdbatools or join our channel on the SQL Server Community Slack.
We have a bunch of videos planned. Let me know if there’s anything you’d like to see.