I know last post was 6 years ago 🙂
Just wanted to clarify that if using the ALTER DATABASE MODIFY FILE method, then taking the db offline as some of the posters have described above, you must COPY the file to a new location, not MOVE it, or you will get an error. This is my script:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TestLogShip');
USE master; --do this all from the master
ALTER DATABASE TestLogShip
MODIFY FILE (name='TestLogShip'
,filename='E:\MSSQL\TestLogShip.mdf'); --Filename is new location
ALTER DATABASE TestLogShip
MODIFY FILE (name='TestLogShip_log'
,filename='E:\DBA_TEST\TestLogShip_log.ldf'); --Filename is new location
ALTER DATABASE TestLogShip SET OFFLINE WITH ROLLBACK IMMEDIATE
--- COPY the physical files !!!
ALTER DATABASE TestLogShip SET ONLINE;
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TestLogShip');