MOVING SQL SERVER FILES

Question

MOVING SQL SERVER FILES

sahoong

SSCrazy

Points: 2545
More actions
July 23, 2009 at 10:58 am

#136729

The gurus,
Could someone please, tell me how I can move my data and log files between two disks? The SQL Server was installed on the C partition and the files have grown more than expected. Now I want to move the files to another partition, probably, M or D but I need help in doing this. I have 3 applications that utilises the SQL Server to store data and I do not want them to be affected by the changes in file location.
Thanks for your assistance.
Regards,
Sahoong.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

I would detach the databases, move the data files, and then reattach the databases. Look up attaching and detaching databases in Books Online or MSDN. This will not affect your application other than they will not be able to connect to the databases while you are moving them. Also you should put the data and log files on seperate disks if at all possible.

Here is a link that may help...

http://msdn.microsoft.com/en-us/library/ms190794.aspx

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 2

My preference is backup/restore (especially if DB files are big in size), risk of files losing/corrupting is also min:

1) full backup- restore with no recovery

2) stop applications (you need this in any case)

3) differential backup- restore with recovery.

That's it. Outage time is min.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

I recommend detach/attach if you've never done this. You could easily mess up your integrity and miss some data to move if you aren't paying attention with backup/restore.