How to Fix: Move MS SQL Databases to Another Drive, Partition

Infopackets Reader Philip S. writes:

" Dear Dennis,

We are running Windows 10 on our office machine which has Microsoft SQL (MS SQL) installed. On that machine we use Microsoft SQL Server Manager to enter in data into our databases, though other machines can access the Microsoft SQL databases through the network using MS Access DSN files. A few years ago we hired someone to set up our databases; however, we are now running out of space on the C drive as the databases have grown significantly in size. We would like to move the MS SQL databases and then set up a RAID to make the data redundant. We currently do not have any backups of the databases. It is my understanding that you cannot simply copy the database files to another disk due to permissions issues and the fact that the databases are live. We are in way over our head and need your help moving the SQL databases to another drive. Can you please help? "

My response:

I asked Philip if he would like me to connect to his server using my remote desktop support service in order to have a closer look, and he agreed.

Below I will discuss my findings.

How to Fix: Move MS SQL Databases to Another Drive, Partition

I have a lot of experience with mySQL databases on Linux and moving those databases are relatively simple - you just shut down the mySQL service, move the data, then edit a config file and/or make a symbolic link to another path and everything works. But that is not the case when it comes to moving MS SQL databases.

By default, when a user installs Microsoft SQL Server to the system, it is installed onto the C Drive. From there, four system databases are created: master, model, msdb, and temp.

The master database contains all of the system level information for SQL Server, including logins, linked servers, endpoints, and other things. The model database is used as a template whenever a new user database is created. The MSDB database is used by the SQL Server Agent, database mail, Service Broker, and other services. The temp database is one of the most important databases and is used for sorting data, snapshots, and other important system activities.

All these databases are required for Microsoft SQL Server to function properly. By default, all other user created databases are installed to the same location (typically C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA, etc).

Now that's said, let's move on.

Ideally when you move the user databases, the system databases should be moved to the same location. The reasoning here is that the system databases are just as important as the user databases. If you are going to make data redundant using a RAID, all databases should be on the same partition.

How to Move MS SQL Databases to Another Drive, Partition

Before we begin, a word of warning -

Moving databases can be extremely nerve wracking. If anything fails along the way you could be dealing with very serious issues that will prevent MS SQL from starting up or your databases to go missing (and the data along with it), or having data pointing to different drive locations.

I suggest you ONLY attempt to do the move if you are confident in your abilities, and can troubleshoot anything that comes your way in case of an error. I also VERY HIGHLY recommend you test the move using a virtual machine (of the server) before applying it to a live machine. That way you can get a feel for any errors that might come your way BEFORE making the changes permanent on the live system. Lastly, do not attempt to move databases during work hours. You will need to do this during a time when it will note interrupt anyone or any programs that use the databases.

If in doubt, hire a professional - such as myself - to manage the move for you. I can connect to your system remotely manage everything. You can contact me using the contact form online the site.

With that said, you can move the databases using the following approach. Note that I have generalized things because each system is different:

1. First, export / backup all system and user databases before proceeding. I also suggest making a disk image backup of the system in case your backups somehow fail.
   
   Example:
   
   ALTER DATABASE [test-db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   BACKUP DATABASE [test-db] TO DISK = 'd:\backups\test-db.bak' WITH INIT, COPY_ONLY;
   GO
    
2. Next, kill all connections to the databases.
   
   /* kill all sessions to database to prepare backups */
   
   USE [master];
   DECLARE @kill varchar(8000) = '';
   SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
   FROM sys.dm_exec_sessions
   WHERE database_id = db_id('MyDB')
   EXEC(@kill);
   
   GO
    
3. Stop the MS SQL service.
   
   NET STOP MSSQL$SQLEXPRESS
    
4. Use Robocopy to copy the master, model, and msdb databases to a new location (example: d:\data)
   
   Example:
   
   robocopy "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA" d:\db msdbdata.mdf /E /ZB /DCOPY:T /COPYALL /R:1 /W:1 /V /TEE
    
5. Launch the SQLServerManager.msc snap-in; select SQL Server Services on the left, then "SQL Server (SQLEXPRESS)" on the right. Right click and select Properties, then go to the "Startup Parameters" tab. Here you need to change (-L, -D, -E) parameters to point to the new Data folder path.
   
   Example:
   
   mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager13.msc
   
   -dC:\Program Files\Microsoft SQL Server\MSSQL1x.SQLEXPRESS\MSSQL\DATA\master.mdf -> -dd:\db\master.mdf
   -eC:\Program Files\Microsoft SQL Server\MSSQL1x.SQLEXPRESS\MSSQL\Log\ERRORLOG -> -ed:\db\ERRORLOG
   -lC:\Program Files\Microsoft SQL Server\MSSQL1x.SQLEXPRESS\MSSQL\DATA\mastlog.ldf -> -ld:\db\mastlog.ldf
    
6. Once that is done, restart MS SQL Service, login to shell, and show all databases to ensure your paths are correct.
   
   /* show all paths for database files */
   
   SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id
   
   GO
    
7. Use the backup and restore method to move your user databases. BE VERY CAREFUL and pay attention to any error messages along the way. Use the ROLLBACK IMMEDIATE command to prep the database before the move.
   
   Example:
   
   USE [master];
   GO
   
   ALTER DATABASE [test-db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   RESTORE DATABASE [test-db] FROM DISK = 'd:\backups\test-db_20190103.bak'
   WITH REPLACE,
   Move 'test-db' To 'd:\db\test-db.mdf',
   Move 'test-db_log' To 'd:\db\test-db_log.ldf';
   GO
    
8. Show all database paths to ensure the move is successful.
   
   /* show all paths for database files */
   
   SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id
   
   GO

I hope that helps.

Once again, this is a general approach so you will need to look at specifics for your case, such as the name of the database as well as the .MDF file locations when exporting the data (I.E. backing it up).

Additional 1-on-1 Support: From Dennis

If all of this is over your head, or if you need help moving your MS SQL databases to another drive or partition, I can help using my remote support service. Simply contact me, briefly describing the issue and I will get back to you as soon as possible.

Got a Computer Question or Problem? Ask Dennis!

I need more computer questions. If you have a computer question - or even a computer problem that needs fixing - please email me with your question so that I can write more articles like this one. I can't promise I'll respond to all the messages I receive (depending on the volume), but I'll do my best.

About the author: Dennis Faas is the owner and operator of Infopackets.com. With over 30 years of computing experience, Dennis' areas of expertise are a broad range and include PC hardware, Microsoft Windows, Linux, network administration, and virtualization. Dennis holds a Bachelors degree in Computer Science (1999) and has authored 6 books on the topics of MS Windows and PC Security. If you like the advice you received on this page, please up-vote / Like this page and share it with friends. For technical support inquiries, Dennis can be reached via Live chat online this site using the Zopim Chat service (currently located at the bottom left of the screen); optionally, you can contact Dennis through the website contact form.