SQL 2000, moving mdf and ldf files to different drive

[kk5]

I have a question in regards to moving mdf and ldf files from one drive to the other on the same
Is there a way to move the files without stopping the SQL services, if so, is it Sp_detach and sp_attach process?
I am planning to stop the sql services and then move the files to a different drive. is it the best practice?
Can anyone please let me know
Thanks

 

[gmmastros]

You cannot just stop the service and move the files. When the service re-starts, it will look for the files in their original locations.

The best approach would be to detach the database, move the files, and then attach them from the new location. You do not need to stop the service to do this.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blombardi]

I actually just went through the same process you are asking about. The link I've posted will show you how to move the systems databases and the user databases.

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

 

[kk5]

Gammastros - Thanks for your reply, but once i move the files to different drive, i will rename the old drives to something else and then rename the new drives as the old ones. that way there shouldnt be any problem.
My question is when we use detach and attach commands, is it good for database size like 150 GB

Thanks

 

[gmmastros]

kk5,

In my experience, the size of the database doesn't matter (for the attach and detach) process. Unfortunately, my experience is with databases less than 1 GB.

My best advice for you is to wait a couple more hours. mrdenny will most likely have something to say regarding this question. He is more familiar with administering SQL Server than I am. He also has more experience with larger databases than I do.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kk5]

thank you George, i will wait for Mr Denny

Thanks
KK

 

[ptheriault]

KK5
You can detach a db of any size. I have detached db's up to 800gb without any problems in the same amount of time it takes to detach smaller db's. Just be sure you don't select the option to update stats as it update the stats on the db before detaching.