In this step by step SQL tutorial I will show you how to move database to a different drive using SQL Server 2008 R2.
Before we began let's clarify why I am doing this. In my case I have virtual drive that has 30GB drive and I am running low on disk space. My biggest files on C drive are SQL Server databases therefore I have decided to move them to F drive that has much more disk space.
NOTE: This tutorial is applicable to certain development machines and should not be used on production servers as usually you require extra steps that involve "moving" folder permissions and all dependand processes.
See below images that shows that I have 877MB left on my C drive and 17.7GB available on F Drive.
My current database files (mdf = data & ldf = log file) are located in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA which is default installation path and below you can see the biggest 3 databases that I will want to move from C drive to F drive. As you can see below my 3 biggest databases will give me extra 1GB free space on C drive.
How to move databases to a different drive
There are diffent ways to move a database from one drive to another one like backup / restore, T-SQL script but in our case we want the easiest and quickest one so we will use DETACH database method using SSMS and simply copy/paste files and ATTACH them back to the database instance.
I opened SSMS, connected to my local instance of SQL Server right clicked one of the databases and selected TASKS and then clicked Detach. See below example.
I got a detach database dialog box where I clicked ok.
NOTE: when you detach database you might noticed that the database does NOT disappear in solution explorer. This is because SSMS doesn't refresh the database list so you might click top level databases folder and click refresh and the detached database should disappear from the list.
I have repeated DETACH operation for the remaining 2 database and now I'm ready to move my database files to new location which in my case is F:\Database\Data
See below example that shows files in current C drive location and new location. Notice that one database has 2 kind of files mdf (=data) and ldf (=log file) so I will move all 6 files.
I cut & pasted the files and below you can see that files in new F drive location.
Now that we have files in new location it is time to ATTACH the database to server. To do that connect to your instance. Right click database folder and click attach.
We are presented with attach database dialog box. I pressed Add button. Find my new F drive location with new files. Selected one database and clicked OK
After clickng ok the dialog box was filled in with information. See example below.
I have repeated the same ATTACH steps for the remaning files and below is screenshot of the disk space after I moved the database files to new drive.
I hope this tutorial will help you with moving your database to new drive.
Take care
Emil
