Migrations are simply a part of a database administrator’s life. And as we start a new year, we probably will have a few migrations to do in the upcoming year. So, rather than shy away from them or have that sickening feeling of dread about them, we should endeavor to become good at doing them, which will make our lives easier. However, as we all know there are many ways to migrate things, and the same way or method may not be the most useful for everyone and in every situation.
One useful tool that I use to help with migrations is PowerShell. Now, I am not suggesting that you go write from scratch PowerShell scripts for everything that you might need to migrate, because this would become a daunting task. And really there is no need to “reinvent the wheel”, because some SQL Server community members have helped us with this. Here are some examples of ways to handle migrations:
- https://github.com/gwalkey/SQLTranscriptase – George Walkey has created a tool called SQLTranscriptase to document your SQL Server using PowerShell. In addition to having your SQL Server documented, which can be tremendously useful in a disaster recovery scenario, these scripts can also be used for migrations and managing different development environments.
- https://dbatools.io – Chrissy LeMaire has lead the development of a PowerShell module that can help you to migrate an entire SQL Server instance with one command.
Now, I will admit that I have used the SQLTranscriptase tools much more than I have used the DBAtools. In fact, I have only used the DBAtools in a testing environment, whereas I have used SQLTranscriptase in a production environment for migrating things to new servers.
*** CAUTION: I always caution that running code from the internet can be dangerous and should be tested in a test environment. This will allow you to understand exactly what is going to happen, before running it in a production environment.
So, whether you decide to use these PowerShell based tools or they just give you some ideas, they should make your life much easier when it comes to migrations.