Skip to content

jstexasdba Posts

Database Migrations – Capacity Planning with dbatools

Have you ever had to deal with system migrations as a DBA and struggle with capacity planning? If so, then this post might be for you. In this instance, I am going to show you how to utilize an amazing PowerShell community tool called dbatools , which can be found on GitHub (g).  If you have not used dbatools in the past and would like to download it, please go to the following URL and read what option might be best for you:

A quick summary of the requirements that I had for this process:

  • Migrate large number of databases from one server to another
  • Ensure storage space is present

I was not able to find any scripts to do exactly what I needed online and decided to utilize what I already had. In that process, though I hit a wall and needed to reach out for help. This is where Fred; @fred (t|b) on the #dbatools slack channel “SQL”; pointed me in the right direction.

Below is the initial script that I started with:

My original output:

The result is not quite, what I was looking for.

You might not be able to see it at first, but with I am missing some things. Some of the dbatools commands have default views similar to how other commands work in PS. The default view provides the most common set of properties for information for the commands.  To expand the command to see all objects in the command you could use the following command:

Using the output of this command, shows me that I am already doing too much work trying to figure out the correct math to use. Previously, I did not realize that the calculated properties are already being done by the dbatools commands.

Another point brought up was the result of the following line:

The result of this output needed more attention, as there was still a white space.  The following addition of .Trim(); solved that problem

To put all of it together I came up with the following:


Woohoo, right, getting results back. I let @fred know that I wanted to write about it because this command helped out a lot. His brain was working out something spectacular and 30 seconds later I received this:

Great results, I now know that I cannot move the database from one server to another on any of the drives shown without expanding the drive volume.  Even though this was an awesome output, I wanted more. I know my data and log drives that I wanted to focus on so I decided to dump everything into an array.

Beautiful, now I know what databases can and cannot be moved for my migration. There are multiple ways that you can output this table.  I decided that I wanted to play with my data in excel so I changed the last line to be:

Now that output will clip it to your clipboard so you can directly paste it in excel and play around with the data. Since, I was able to determine what systems needed what, I can now proceed with my migrations and not have to worry about if I have enough space or not.

Leave a Comment