Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
heze
Posting Yak Master
192 Posts |
 Posted - 2007-05-17 : 07:44:55
|
| Hi, I am working on a new installation which I did not set up and realized was using the wrong partition of the server to store the data and log files, I have already created several databases, I want to use another partition for these databases without having to drop them and create them all over again.In BOLine i saw this command but want to make sure its safe, hope somebody can comment on this or if I am missing something.thank youproposed command:"MODIFY FILE ( NAME = logical_file_name, FILENAME = 'new_path/os_file_name ' )" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-17 : 08:31:10
|
| That should work.Backup the database first just in case (I would do a backup and restore to do this).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-05-17 : 08:40:44
|
| Another possible option is to detach the database, move the data files to the new location, then attach the database again pointing to the new location of the data files.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-05-17 : 10:58:32
|
| thanks nr, shelperI tried shelper's suggestion and it works fine, Im sure thres a catch and thats the reason for the backup option, |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-05-17 : 11:24:25
|
| my question is now:can I move all the data files including the system and software installed (eg proclarity) databases freely?is the database-to-file mapping transparent to the applications that use the databases?thank you |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-05-17 : 12:11:31
|
| yet another question:what is the impact of doing:MODIFY FILE ( NAME = logical_file_name, FILENAME = 'new_path/os_file_name ' )on the tempdb database to change the data file from c: to d: for example?thank you |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-17 : 16:22:20
|
| You can move any db, follow steps in Microsoft kb article 224071 'How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server'. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-19 : 07:08:34
|
| "I tried shelper's suggestion and it works fine, Im sure thres a catch and thats the reason for the backup option"The catch is that you should make a backup first in case the re-Attach doesn't work for any reason!Kristen |
|
|
|
|
|
|
|
|
moving the data file to another location