Change SQL server 2008 DB location

ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE my SET OFFLINE;

ALTER DATABASE my MODIFY FILE

(

   Name = my_Data,

   Filename = 'D:\DATA\my.MDF'

);

ALTER DATABASE my MODIFY FILE

(

   Name = my_Log,

   Filename = 'D:\DATA\my_1.LDF'

);

Now here you must manually move the files from their current location to D:\Data\ (and remember to rename them manually if you changed them in the MODIFY FILE command) ... then you can bring the database back online:

ALTER DATABASE my SET ONLINE;

ALTER DATABASE my SET MULTI_USER;

This assumes that the SQL Server service account has sufficient privileges on the D:\Data\ folder. If not you will receive errors at the SET ONLINE command.

from http://stackoverflow.com/questions/6584938/move-sql-server-2008-database-files-to-a-new-folder-location

  

last thing to mention if you cannot bring the DB online, try to attach it manually (this worked for me)