Moving SQL Server database files to a new location

STEPS TO MOVE FILES USING ALTER DATABASE:

1.   Run query to find the current names of database:

select name, physical_name
from sys.master_files
where database_id = db_id('AdventureWorks')

2.   Run the alter database command:

Once the filename and physical_name have been determined, the database can be moved using ALTER DATABASE with the MODIFY FILE command as follows:

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, 
FILENAME = 'D:\SQLData\AdventureWorks_Data.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, 
FILENAME = 'D:\SQLData\AdventureWorks_Log.ldf');

3.   Take the database offline and online:

Once you have run the above statements, to complete the move, set the database offline:

ALTER DATABASE AdventureWorks SET OFFLINE

and then move the data files to the new location, then bring the database back online:

ALTER DATABASE database_name SET ONLINE