Site icon Eitan Blumin's blog

T-SQL Tuesday #143 – Short Powershell code to move DB files in AlwaysOn

This month’s #tsql2sday is hosted by John McCormack who asks us to share some useful snippets of code that can help us in our everyday jobs.

Well, honestly, this is more or less what my blog is mostly about anyway: sharing insights, scripts, and code snippets that I found to be helpful in my day-to-day job as a SQL Server consultant.

Link back to the invitation post

First, I must apologize for the late publication and for my lack of activity during the past couple of months. September was pretty much full of holidays over here in Israel and we were super busy, so that barely left me any time for my blog. Just so you’d know to appreciate this, I’m writing this post at the cost of sleeping hours! I’m doing this for you, my friend!

Anyways, back to the topic at hand.

I can reference back to lots of very useful scripts and snippets that I find to be invaluable, and I’m using them quite frequently. For example:

I could go on and on, but you can just go visit my blog archives and see for yourself if there’s anything there that could interest you.

Also, we at Madeira Data Solutions have an amazing collection of useful scripts and snippets in our open-source GitHub repository called the “Madeira Toolbox”. I’m sure you’ll find there lots of things you’d like. Be sure to watch/subscribe to that repository too, as we continuously add new stuff there and keep our scripts up-to-date.

Something New for Today

But I’m not here just to re-hash old content. I’ve been doing interesting work this past month, and it would be a waste not to share an interesting script with you as a result, right?

Today, I’m sharing with you a cool Powershell script that basically implements the methodology necessary to move database files to a new location in AlwaysOn Availability Groups, without breaking HADR.

It’s based on a few very useful step-by-step guides on the topic such as this one and this one and this one. But it takes it a step further by being a single cohesive Powershell script that does everything end-to-end.

Well… Almost everything… The only thing it’s missing is somehow disabling any SQL Agent jobs that may be performing backups. I still haven’t figured out how to possibly automate such a thing, so you’d have to do that manually on your own.

But other than that, the script does the following:

I know, it’s (ahem) a bit more than a “snippet”, but you gotta admit it’s pretty cool and useful, right?

A few remarks:

Anyways, without further ado, you can find this script in our Madeira Toolbox here, or in my GitHub Gists below:

Exit mobile version