Move User Database Files in SQL Server

As part of Database maintenance , we might have to move the database files to different disk drives/volumes. This could be because of a variety of reasons like the database has grown and the current drive cannot hold it etc.

This cane be accomplished using atleast two ways:

• Take the database Offline, move the file and alter the master data, bring it online
• Detach and Attach

Offline Approach:

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.
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
   

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>');

This approach has a few problems when many active users are connected to the database.
There are recommendations to put it in single user mode
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
This can be troublesome if another admin gets hold of the session before you do.
Also, sometimes processes might block you from becoming the single user , "SQL server Transact Intellisense" is one weird one that I encountered once.

Attach/Detach Approach:

This is very simple,

1. Detach the database , tick the DROP Connections Check Box to disconnect any user connections.
2. Move the file(s)
3. Attach the database, select the appropriate files.