Migrations – PowerShell Can Help

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.

This entry was posted in DBA, PowerShell and tagged , , . Bookmark the permalink.

1 Response to Migrations – PowerShell Can Help

  1. Hey Mike, thanks for the plug. Ill be speaking on these scripts at the SQL Saturday 2017 in Raleigh, NC in March here http://www.sqlsaturday.com/592/EventHome.aspx. I REALLY came up to speed on the Shell developing these and am finding more things you can do with the great SQL/PShell combo. We practically do all in Shell these days and have seen SSIS go by the wayside. Thanks again George Walkey http://www.insyncva.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s