When you plan to move user databases files to new location please follow the steps.
1. Make sure users are disconnected and planned down time
2. Make sure to take a Full backup of the databases
3. Find the data files and log files names and current location
use master
go
SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('My_dbname')
4. Set the database you want to work with offline
ALTER DATABASE My_dbname SET OFFLINE
(OR)
ALTER DATABASE My_dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
go
5. Move one file at a time to the new location
ALTER DATABASE My_dbname
MODIFY FILE ( NAME = My_Data, FILENAME = 'E:\MSSQL\Data\My_Data.mdf')
go
6. You move the data (MDF) to new Drive/Location
7. Set the database online
ALTER DATABASE My_dbname SET ONLINE
go
go
5. Move one file at a time to the new location
ALTER DATABASE My_dbname
MODIFY FILE ( NAME = My_Data, FILENAME = 'E:\MSSQL\Data\My_Data.mdf')
go
6. You move the data (MDF) to new Drive/Location
7. Set the database online
ALTER DATABASE My_dbname SET ONLINE
go