Move a user databases

To move a data or log file as part of a planned relocation, follow these steps:

1. Run the following statement.
   
    

   ALTER DATABASE database_name SET OFFLINE;  
   
   

2. Move the file or files to the new location.
3. For each file moved, run the following statement.
   
    

   USE master
   GO
   ALTER DATABASE AdventureWorks2012
   MODIFY FILE 
   ( NAME = AdventureWorks2012_Data, 
   FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path
   
   USE master
   GO
   ALTER DATABASE AdventureWorks2012 
   MODIFY FILE 
   ( NAME = AdventureWorks2012_Log, 
   FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path
   

4. Run the following statement.
   
    

   ALTER DATABASE database_name SET ONLINE;  
   
   

5. Verify the file change by running the following query.
   
    

   SELECT name, physical_name AS CurrentLocation, state_desc  
   FROM sys.master_files  
   WHERE database_id = DB_ID(N'<database_name>');