This is a step by step tutorial on how to change the MS SQL databse file names (file.mdf and file.ldf) or their location. The items in red need to be replaced with the names used in your database. This has been tested on SQL 2005/2008/2012.
The method will work for renaming the whole DB, its files and logical units or you can use Steps 3-6 only to change the location of the .mdf and .ldf files:
1) Rename the actual database MYDB ==> MYDBold in the SSMS (SQL Studio Manager) (This is only necessary if you want to rename the actual DB as well)
2) Open Query window for the MYDBold and Change the logical names in the DB. These names can be found from right clicking on the DB -> Properties -> Files tab:
ALTER DATABASE MYDBold MODIFY FILE (NAME = MYDB_data, NEWNAME = MYDBold_data);
ALTER DATABASE MYDBold MODIFY FILE (NAME = MYDB_log, NEWNAME = MYDBold_log);
3) Alter files for DB, changing to the new .mdf and .ldf file location:
GO
ALTER DATABASE MYDBold MODIFY FILE (NAME =MYDB_Data, FILENAME = ‘D:\SQL_Data\MYDBold.mdf‘)
GO
ALTER DATABASE MYDBold MODIFY FILE (NAME = MYDBold_log, FILENAME =’D:\SQL_Data\MYDBold.ldf‘)
GO
4) Take the DB offline in SSMS (Right click on the DB -> Tasks -> Take Offline). (If DB takes very long to go offline, you can try to Detach with Drop/Update checked). This will allow the DB to go offline.
5) Change the actual physical file names on the hard drive
6) Bring the DB back online, and whola! Your DB is now attached to the new storage device