question

NanPrashant avatar image
NanPrashant asked

How do I move existing DB files directory to the new directory

Hi All, I have a space issue in one of the server's drive where DB is storing all its log and mdf files. Now I wanted to change default location to store the all new databases which will be created in future and existing one needs to be moved to that new default location. I have achieved the first one i.e changing the default location can any one suggest me how to achieve the second one.. Thanks Prashant
database-filesmovefile location
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs by you voting. For each helpful answer below, click on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the thumbs up next to that answer.
0 Likes 0 ·
Squirrel avatar image
Squirrel answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
Another possible mechanism is to use [DETACH/ATTACH][1] to move the files. I'm not sure which of the two processes will work faster, but before doing either I'd strongly suggest getting a good backup. [1]: http://technet.microsoft.com/en-us/library/ms190794.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 definitely. Always take a backup no matter what you are doing! Been there, done that, burnt fingers.
2 Likes 2 ·
Kev Riley avatar image
Kev Riley answered
To just add a little more clarity to the answer given by Squirrel, there are 2 approaches. Either - [backup and restore][1] to new location - [detach and attach][2] in new location The approach taken will be determined by how much disk capacity you have (i.e. can you afford to have multiple copies) and how much downtime is allowed or needed. If you are moving system databases, then further considerations need to be taken, and will require some downtime. [1]: http://technet.microsoft.com/en-us/library/ms187048.aspx [2]: http://technet.microsoft.com/en-us/library/ms187858.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.