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