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:
- An account within the local administrator’s group
- A SQL Server instance
- SQL Server Management Studio
- 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
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
that man is a genius. too bad there’s a grammatical error in the last line. and that’s the one people remember…
I’m not so sure it wasn’t intended, but it is quite funny..
Thanks, nice post
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.
after moving DB to another Drive its showing Read only, in that case whats the solution
After attaching, the property –> option –> state –> db read only must be set to False.
After attaching, the property –> option –> state –> db read only must be set to False.
How come nobody is pointing out how offensive his latently racist euphemism is?
Hi, can you please point the objectionable matter to me? I will edit it out.
Worked like a charm. Thanks!
Thanks worked wonderfully!!
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.
Thanks. I just removed the paragraph referring to the water fountains, which in any case was superfluous, imo.
Superfluous, yes, but the apparent humor of it made your post MUCH more enjoyable. I, for one, liked it.