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.
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.
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:
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.
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.
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."
The hyperlink in the Message column can be found on the 'Attach Databases' screen as the far right column.
Clicking on this link displays an obscure message. "Unable to open the physical file... Operating System error 5: 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.
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.
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.
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.
Microsoft has some good information at these links: