Moving a Multi-TB Database From One Disk To Another

Published On: 2014-11-19By:

Recently a client brought me a challenge. They wanted to more a database from one LUN to another. So far this is pretty straight forward. The trick here is that this is a multi-TB instance with several databases which are about 1TB in size, and the instance has a two hour per night maintenance window.

So the trick here is how to move all the databases (including the system databases) to the new disk within the acceptable outage window. Making things more complex, the database instance is a Clustered SQL Server 2014 instance.

Log shipping would work, but that’s a pretty complex migration strategy for a simple LUN migration.

The easiest solution in this case was to add new database files and let SQL Server move the data. How I did this went something like this.

1. Mount the new LUN to the cluster and format it. In this case as the F: drive (the databases are currently on the E: drive).
2. Robocopy everything except for the database files to the new drive. This has the nice side effect of creating all the folders which are needed.
3. Add a new data file for each of the large databases on the F: drive.
4. Use the DBCC SHRINKFILE command with the EMPTYFILE parameter to remove all the data from the main data files to the new data file.
5. Once the DBCC SHRINKFILE command is done (it actually throws an error because the system objects can’t be moved). Shrink the main database files down as small as possible. I got most of them down to a few megs in size.
6. Change the auto growth settings on the existing file to be disabled so that it can’t grow beyond this small size.
7. Repeat this for all the databases in question.

At this point we wait for the maintenance window.

8. Stop the SQL Server service.
9. Robocopy the database files to the new drive.
10. Remove the drive letter from the old volume.
11. Change the drive letter on the new volume to the new drive.
12. Start SQL Server
13. Log into SQL Server and use the ALTER DATABASE … MODIFY FILE command to change to filename from the new drive letter to the old drive letter.
14. Restart SQL Server
15. Use the DBCC SHRINKFILE command with the EMPTYFILE parameter to remove all the data from the new data files to the main data file.
16. Drop the new data files from the database.

Using this approach I was able to move several terabytes of databases from one LUN to another with about 20 minutes of total outage to the system.

Now this does require that there’s a good amount of free space on the new volume as you have to be able to have the two database files on the volume at the same time. But a little drive space is a small price to pay for a short maintenance window.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link