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