12 Days of dbatools – Twelfth Day4 min read

---

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

On the tenth day of Christmas dbatools gave to me: Restore-DbaDatabase

On the eleventh day of Christmas dbatools gave to me: New-DbaAvailabilityGroup

On the twelfth day of Christmas dbatools gave to me: Start-DbaMigration

---

Command Information:

The final day is upon us and I have saved the best for last and one you can take in to the holiday season as a present from our fearless leader of dbatools.  The Start-DbaMigration is where the dbatools module started years ago.  Who of us have not said “I really should automate this tedious SQL migration stuff” so I do not forget a step when I have not had enough coffee.  Well Chrissy LeMaire (b | t) not only said it, but made it reality.  This early work has grown in to the dbatools module that we know today and has grown in to a multi-tool toolkit that help the DBA expand their skills without having the burden of learning a scripting language and the complexity that goes with hooking it to your SQL Server.

This tool has literally changed my life since finding it a couple years ago.  Not only am I doing work in PowerShell, I am automating work processes.  Also, this has contributed to my personal growth.  Chrissy and team has such a welcoming atmosphere and inclusion around the project, it has guided me to contribute to an open source project as well as guided me to presenting at SQL Saturdays events.

Command Description: 

Start-DbaMigration

Start-DbaMigration consolidates most of the migration tools in dbatools into one command. This is useful when you’re looking to migrate entire instances. It less flexible than using the underlying functions. Think of it as an easy button. It migrates:

All user databases to exclude support databases such as ReportServerTempDB (Use -IncludeSupportDbs for this). Use -Exclude Databases to skip.
All logins. Use -Exclude Logins to skip.
All database mail objects. Use -Exclude DatabaseMail
All credentials. Use -Exclude Credentials to skip.
All objects within the Job Server (SQL Agent). Use -Exclude AgentServer to skip.
All linked servers. Use -Exclude LinkedServers to skip.
All groups and servers within Central Management Server. Use -Exclude CentralManagementServer to skip.
All SQL Server configuration objects (everything in sp_configure). Use -Exclude SpConfigure to skip.
All user objects in system databases. Use -Exclude SysDbUserObjects to skip.
All system triggers. Use -Exclude SystemTriggers to skip.
All system backup devices. Use -Exclude BackupDevices to skip.
All Audits. Use -Exclude Audits to skip.
All Endpoints. Use -Exclude Endpoints to skip.
All Extended Events. Use -Exclude ExtendedEvents to skip.
All Policy Management objects. Use -Exclude PolicyManagement to skip.
All Resource Governor objects. Use -Exclude ResourceGovernor to skip.
All Server Audit Specifications. Use -Exclude ServerAuditSpecifications to skip.
All Custom Errors (User Defined Messages). Use -Exclude CustomErrors to skip.
Copies All Data Collector collection sets. Does not configure the server. Use -Exclude DataCollector to skip.

This script provides the ability to migrate databases using detach/copy/attach or backup/restore. SQL Server logins, including passwords, SID and database/server roles can also be migrated. In addition, job server objects can be migrated and server configuration settings can be exported or migrated. This script works with named instances, clusters and SQL Express.

By default, databases will be migrated to the destination SQL Server’s default data and log directories. You can override this by specifying -ReuseSourceFolderStructure. Filestreams and filegroups are also migrated. Safety is emphasized.

Command Example:

Here you see a before and after shot of the SQL2016 and SQL2019 instances.  SQL2019 is a fresh install and has no databases or logins.  Once the migration completes you can see a list of what was migrated and then you see the final result of all of your precious databases and settings on the new SQL2019 serer.  YOU ARE WELCOME!!!

12 Days of dbatools – Recap:

I hope you made it through the 12 posts and enjoyed what I have presented.  dbatools has really become a daily staple for me to use in my work environment and has saved me countless hours.  Sure TSQL can do most of what dbatools can do, but in my opinion dbatools is more scaleable in so many ways.  And with the wave of the future now upon us with automating all the things, this is a true building block for that.

I encourage each of you that made it this far down the post to reach out on Twitter using #dbatools to let everyone know what your favorite dbatools command is.  Look forward to seeing anyone’s feedback that is brave enough to send that tweet.