Move a SQL Server Database

Step by step how to move a SQL Server Database using SQL Management Studio

Seann Hicks

Seann Hicks

Sunday, June 28, 2020

How to Move a SQL Server Database

Today I ran into a problem with my C: drive running out of space. It's an older SSD drive that I purchased about 5 years ago, and only has a 256 GB capacity. Over time it has accumulated a lot of software and some data. I also have a 1TB HDD E: drive (the D: drive is my CD drive, yes I have an internal CD ROM drive!). I have SQL Server installed along with some large databases all stored on the C: drive in the default path:

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

An SSD drive performs best if it have about 25% free space or more. I had about 24MB free, and I wasn't able to perform OS updates, or other software updates anymore. In this post I describe the steps that I followed to move my multi-gigabyte SQL Server databases to the E: drive.

To see where your databases are stored on disk, in SQL Management Studio (SSMS), select the database, right click and select 'properties'. in properties, choose file and you will see the following details about the selected database.

SQL Server Database File Location in SSMS

Close Database Connections

In my case the databases I moved are used by a web application. In IIS I stopped the application pool and the site that use the databases. If you don't have access to the application connecting to your database you can set it to single user mode and force the remote connections to close. Keep in mind that this will cause a service outage and might cause transaction rollbacks if there are in progress data updates.

Detach the Database

In order to host the databases from an alternative location to the default I had to detach them. Backup and Restore or creating a Bacpac file allows you to move databases from one Database Server to another (like moving from one environment to another).

To detach a SQL Server database, in SSMS right click on the database and select 'Tasks' -> 'Detach...'. This will open the following screen:

Detach SQL Server Database Options in SSMS

Once the detach operation is complete, the database should no longer be visible in SSMS. The MDF and LDF files can be moved to the new location. I did a file copy to an exactly parallel location on my E: drive.

E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

You can of course choose any place to store the MDF and LDF files.

Attach the moved Database

Once the database files had been moved, from SQL Studio I attempted to attach the database. From SSMS, right click on 'Databases' to display the context menu and select 'attach...'. The attach screen is displayed. Click 'Add..." to select the newly moved MDF file. In the screen shot below I have selected my example database file from its new home on the E: drive.

Move SQL Server Database Attach Moved Database

However, when I click "OK" to re-attach my database, I get this obscure error.

"An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details."

SQL Server Attach Database Error

The hyperlink in the Message column can be found on the 'Attach Databases' screen as the far right column.

 SQL Server Attach Database Error Message Link

Clicking on this link displays an obscure message.  "Unable to open the physical file... Operating System error 5: Access is Denied.

SQL Server Attach Database Error Access is Denied 

Fortunately, this is easily resolved. The MDF and LDF files I copied cannot be accessed by the account I using to log into SQL Server.  By adding "Full Control" access to the MDF and LDF files through the file manager security app for my local account I was able to resolve this error.

Security App Grant Full Control to SQL Server Account

The local account I am using is called "root", so I have granted full control to that account for both the MDF and LDF files. These are the permissions on the source MDF and LDF files. So the other permissions that are added with the file copy can be removed, in keeping with the 'least privelege' security practice.

SQL Server Attach File Permissions

Verify the Attached Database

Once the permission problem was cleared up, the database attachment was successful. The example_database now appears in SSMS and if I right click and view it's file properties I can see it is being served from the E: drive.

SQL Server Attached Database 

Success! I restart the application pool and IIS site and the database is working as expected.

I can now remove the database files from my C: drive and free up valuable space. Don't forget to empty the recycle bin.


The simplest way to move a SQL Server database is to detach, move and attach it. Just watch for file permission issues that may happen as a result of the file copy. My example is very specific to my setup. I used integrated security with a local admin account instead of a SQL account. I am not sure if the SA account would run into a file permission error like I did.

Sources Cited

Microsoft has some good information at these links:

Photo by Elevate on Unsplash

Sign-up to receive the weekly post in your inbox. I don't share email addresses, unsubscribe at any time.

Email Address