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.

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:

• The Simplest Alternative to sp_MSforeachdb
• Finding a use for Extended Properties in SQL Server
• Using Extended Events as a Buffer
• T-SQL Script to Fix Orphaned DB Users Easily
• Find and fix untrusted Foreign Keys in all databases
• Re-align Identity Last Value to Actual Max Value
• Remove the IDENTITY property from an existing column

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:

• Makes sure the destination folder(s) exist.
• Suspends and removes the specified database from its Availability Group.
• Executes ALTER DATABASE .. MODIFY FILE .. to change the database file paths.
• If connected to the PRIMARY replica: Takes the database offline.
• If connected to the SECONDARY replica: Takes the whole MSSQLSERVER service down.
• Actually moves the files to their new destination, while retaining the file permissions and ownership (so that the SQL Server won’t get an “Access Denied” error).
• Brings the database / service back online.
• Adds the database back to the Availability Group.

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:

• Note the parameters for the script. Be sure you’re providing the proper values as input.
• The script was not tested with database files that aren’t mdf/ndf/ldf (such as FILESTREAM, Full-Text Catalogs, and In-Memory). So, I cannot guarantee that those will work. But if you’re feeling adventurous, you can use the switch parameter “AllowNonDataOrLogFileTypes” to try it out anyway.
• If you’re moving large files, remember that it would take a while to finish, during which time the database will not be available.
• Don’t forget to disable backup jobs! The script doesn’t do that for you.
• The script is NOT idempotent. If it fails in the middle of execution, you may have trouble running it again.
• As always with such things: Test, test, test! I recommend adding a new, small database to your Availability Group and test the script on this database, before trying it out on your larger production databases.
• The script utilizes the same template I used in my post One handy Powershell script template to rule them all. So, it also has some useful logging.

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