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.
Sure segregation is something viewed in human society as morally wrong but not on your computer. There’s a tremendous amount of water fountains when dealing with this type of situation. Selecting which water fountain you need to utilize sometimes is tricky and that isn’t going to be covered here. We’re moving a database, perhaps if you’re lucky, the water fountain selection may be covered at a later time.
This sounds ridiculously simple. Take a database and put it somewhere else. If done incorrectly in a production environment you yourself may have to use the dirty disgusting water fountain outside the office building you were previously allowed to enter.
Move Microsoft SQL Server Database
Several things you’ll need:
- An account with in the local administrators 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 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 in to 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… that water dirty water fountain may be in the future. There’s always event viewer.
Link of Interest: SQL Server Downloads via Microsoft.
Guest Post By: Greg Partlow
