How to move Microsoft SQL Server database to another drive partition

Information is stored somewhere. Sometimes it’s not suited for its original location. If you’re running low on disk space, added a new drive array, or just curious; this is a simple procedure to move a database(s) in Microsoft SQL Server to that new desired location, painlessly. Obviously maintain best practice procedures in the proper placement of said DB. It’s always a swell idea to have your databases segregated to allow for maximum I/O.

Move Microsoft SQL Server Database

Several things you’ll need:

  1. An account within the local administrators group
  2. A SQL Server instance
  3. SQL Server Management Studio
  4. SA privileges to your SQL Server

This would probably work:

Locate the directory of your SQL Database needing to be moved. By default (which isn’t a good practice) the location of your databases are here:

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

move Microsoft SQL Server database
Unlike yours, this particular one is located here. I’d double check but if you’re ridiculously confident don’t bother.

Now, go into SQL Server Management Studio. Locate the database you intend to move and right click that bad boy. Move your mouse down a bit until you get to Tasks. Let that menu pop out and click Detach.

This here window comes up. If there are active connections, it’d probably be best to tell those users to hurry up since you’re busy doing some nerd work to allow them to be more productive. If they refuse, there’s a check box to kick them out. I tend to check that box regardless.

Then click OK.

Ok, now go back to that directory where the database is located. Select both the .MDF and LDF files. Cut and Paste them into the new location, hopefully making note of that new location since you will need it.

So it may take a while to copy to its new location. Find something else to do during this time. It wouldn’t hurt to read various other articles published by The Windows Club. Once it’s there go back into SQL Server Management Studio. Right-Click the databases Folder, The click Attach.


Locate the new location of your database, then select the database and click OK.

Click, OK. Click OK on the next window. Hit that F5 Key to refresh the SQL window. If your database is there, then you did a great job. Otherwise…There’s always event viewer.

Link of Interest: SQL Server Downloads via Microsoft.

Guest Post By: Greg Partlow

Posted by on , in Category General with Tags
This post has been submitted by a Guest Author. If you would like to submit a guest post, you may contact me on the mail ID mentioned in the About page.

14 Comments

  1. that man is a genius. too bad there’s a grammatical error in the last line. and that’s the one people remember…

  2. @vTechie

    I’m not so sure it wasn’t intended, but it is quite funny..

  3. Binh Thanh Nguyen

    Thanks, nice post

  4. Hue

    OK, but what to do when on Locate Database Files doesn’t show me other drive than C. And if i execute attach command it says: “Directory lookup for the file “D:TESTTest.mdf” failed with the operating system error 5(Access is denied.).”. The user is admin on the PC and i checked all boxes for server roles. My guess is Access is denied is related to the Drve, not he file. I check Advanced security settings for the drive, but it doesn’t allow me to change – i only see that the user has full control.

  5. Girish Wali

    after moving DB to another Drive its showing Read only, in that case whats the solution

  6. Antonio Augusto Castillo

    After attaching, the property –> option –> state –> db read only must be set to False.

  7. Antonio Augusto Castillo

    After attaching, the property –> option –> state –> db read only must be set to False.

  8. Joe Walker

    How come nobody is pointing out how offensive his latently racist euphemism is?

  9. Hi, can you please point the objectionable matter to me? I will edit it out.

  10. Marius

    Worked like a charm. Thanks!

  11. Harry Bosch

    Thanks worked wonderfully!!

  12. victor eigen

    Anand, I believe that Mr. Walker finds the METAPHOR of water fountains, as a reference to racial segregation, to be “latently racist”, whatever that means. I see it as a reference to a time when racism was endemic to our country. Some people see no humor in such historical references. Or perhaps it is the very word; segregation, that he finds objectionable.

  13. Thanks. I just removed the paragraph referring to the water fountains, which in any case was superfluous, imo.

  14. victor eigen

    Superfluous, yes, but the apparent humor of it made your post MUCH more enjoyable. I, for one, liked it.

Leave a Reply

Your email address will not be published. Required fields are marked *


1 + 3 =