Member-only story
How can I move a MSSQL database to another drive?
I am currently working on a project that has been running for a couple of years and the database has grown considerably over that time. The database takes up about 100GB of disk space, which has caused my system disk to fill up regularly and SQL queries to abort.
Since I have two more hard disks, it was obvious to move them.
Here is a little tutorial on how to move a database to another drive.
Let’s call our database Birds and move it from drive C to drive F:\databases\.
The first step is to tell our master database that the database is now on F:
USE master;
ALTER DATABASE Birds MODIFY FILE(name=’Birds’,filename=’F:\databases\Birds.mdf’);
ALTER DATABASE Birds MODIFY FILE (name=’ Birds_log’,filename=’F:\databases\Birds_log.ldf’);
Remember to move the log file as well.
Next we need to take the database offline. This is done with the command:
ALTER DATABASE Birds SET OFFLINE WITH ROLLBACK IMMEDIATE;
Without this we cannot move the file.
But that is exactly what we can do now, by moving the two files Birds.mdf and Birds.ldf into their new directories.
Once this is done, we can bring the database back online.
ALTER DATABASE Birds SET ONLINE;
Remember that the current user must have full rights to the file path. (Full control)