July 11, 2008 at 12:13 pm
A contractor created a DB in the default location and I was wondering if there is a way to move the files once they are created to a more suitable location.
Thanks
Quinn
July 11, 2008 at 12:26 pm
Yep.
Either detatch the database, move the files to where you want them and reattach.
Or do a full backup of it, and restore it over itself usuing WITH MOVE in your restore script.
Either way, the database will be down for a short time during this, so take that into account.
The Redneck DBA
July 11, 2008 at 12:31 pm
Thanks for the quick responce Jeremy. I will give it a try.
Quinn
July 11, 2008 at 2:13 pm
Jason
I detached the database and moved the files. During the reatach it didn't see the db files in the file tree so I just typed them in and and then changed the location on the files in the lower box. When I try to add I get a message saying the log file already exists. Change the file name or path name and retry.
Any ideas what could be doing that? The files are named wsgis.mdf and wsgis_log.ldf
Quinn
July 11, 2008 at 2:23 pm
That fact that you don't see them in the tree is odd...it should show up anything with a .mdf extension (or if you select All Files, it should show them all). Did you rename the files themselves, or just move them?
Quinn (7/11/2008)
JasonI detached the database and moved the files. During the reatach it didn't see the db files in the file tree so I just typed them in and and then changed the location on the files in the lower box. When I try to add I get a message saying the log file already exists. Change the file name or path name and retry.
Any ideas what could be doing that? The files are named wsgis.mdf and wsgis_log.ldf
Quinn
The Redneck DBA
July 11, 2008 at 2:26 pm
Just move them.
Quinn
July 11, 2008 at 2:32 pm
I'm not sure what's going on then. The files may have been corrupted in the transfer?
Did you move between drives on the same server...to a different server...just to a different folder? Are you sure those are the correct files for that datbase? Are you sure there aren't more than two files for that database?
You can also just restore a backup using WITH MOVE to have it put the DB in another folder.
The Redneck DBA
July 11, 2008 at 2:54 pm
I just copied the files to another logical drive on the server. I left the system db's in the original directory. Do I need to copy them as well? I moved the db files to yet another directory. Now I am seeing the files in the dialog box. When I select them and try to attach it gives me an error now that says one exists.
Q
July 11, 2008 at 3:00 pm
Refresh your SSMS display of databases and see if it actually attached despite your original error. It may have attached and then threw an error with some post-attach step.
The Redneck DBA
July 11, 2008 at 3:00 pm
It looks like I may have got it. The last time I tried it it created the db and it looks like all of the tables area there and data is in the table.
We will check again Monday.
Thanks
Quinn
July 11, 2008 at 4:13 pm
to attach the database, copy the files to the desitred location and use the following T-SQL
sp_attach_db 'dbname', 'drive:\path\filename.mdf', 'drive:\path\fiilename_log.ldf'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply