Move your sql database files to a separate hard drive

My company provides me with a development server for my work. Here runs my local version of our application with my git branches for development. This server also runs an MSSQL Server which provides me with a copy of our production database. After some time the free disk space on this server hard drive became smaller and smaller, so I wanted to clean it up.

But I realized that there was not much I could clean up. I noticed the large size of the database files, > 30GB. So I asked our IT infrastructure team to add more disk space to my development server.

As a result, they kept the size, but provided me an additional hard drive, and asked me to move the DB files to the additional hard drive.

So I did some research on how to move only the database files to a different hard drive. Here is what I found out and worked for me well.

1. where are the relevant database files?

First, we need to find out where the relevant database files are stored so that we can copy them to the new hard drive. I found out that you need to move two files, a .mdf and .ldf file.

The .mdf file contains the schema and data. The .ldf file contains the logs. This StackOverflow post gives you a brief overview of these file types.

So now we know which files to copy, we can get the path by using the following SQL query.

select * from [YOUR_DATABASE_NAME].sys.database_files

This will give you the following output (I blurred sensitive information):

2. copy the files to the new hard disk

Now, that we know the path we have to take the database offline to copy the files. Otherwise, you will get an error message while copying, because the files are in use of the database.

I use the SQL Server Management Studio. Here you have to do a right-click on your database, go to tasks, and then click "Take offline". After that you will see something like this:

You have to select the checkbox in the middle. After the database is offline you can copy the two files into your target path on the new hard drive.

Then bring the database online again.

3. pointing to the files in the new path

To point the mssql server to the new file path you have to run two queries, one for each file. For the .mdf file:

ALTER DATABASE [YOUR_DATABASE_NAME]
MODIFY FILE (
    NAME = 'YOUR_NAME_Data',
    FILENAME = 'E:\SQL_DATABASES\YOUR_FILENAME.mdf'
)
GO

And for the .ldf file:

ALTER DATABASE [YOUR_DATABASE_NAME]
MODIFY FILE (
    NAME = 'YOUR_NAME_Log',
    FILENAME = 'E:\SQL_DATABASES\YOUR_FILENAME.ldf'
)
GO

The FILENAME is self-explanatory. The correct NAME value can be found when you do a right-click on the database, then properties. There you can go to Files and will see your individual NAME values in the Logical Name column.

After you run the ALTER query you will get something like this (for the .ldf file as an example):

Very good!

Now we can run the SELECT query from above again and you should get the new paths.

4. restart the SQL server

In my case, I had to do a restart of the SQL server. After that, you also see the new file paths in the properties of the database.

5. free disk space

Now you can delete the .mdf and .ldf files in the origin directory. This is a double-check of your work at the same time because you can not delete these files if they are still in use.

Well done. Your database files are moved to the new hard drive and you are looking forward to new free disk space.

6. Conclusion

I never had to do something like that before. I had fun researching a solution and making it all work in the end. Maybe there are better approaches to this and other best practices. If so please comment and/or reach out. I am happy with any kind of feedback.

Happy Coding!