How to move the User database file to different location

Run the following statement.

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

SELECT db_name (database_id) DBName,

physical_name AS CurrentLocation,

name as LogicalName, state_desc 

FROM sys.master_files 

WHERE database_id=100

2.     Take the Database Offline.

CHECKPOINT

USE master;

GO

ALTER DATABASE AdventureWorks2012

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

Detach the database

3.     Move the file or files to the new location.

Physically move the file or use ROBO copy command

4. For each file moved, run the following statement.

ALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = AdventureWorks2012_2, FILENAME = 'D:\USER_DATA\DATA_07\ AdventureWorks2012.ndf'); -- We have change the actual physical path

5. Bring the Database online.

Attach the database; 

USE master;

GO

ALTER DATABASE AdventureWorks2012

SET READ_ONLY;

GO

ALTER DATABASE AdventureWorks2012

SET MULTI_USER

WITH ROLLBACK IMMEDIATE;

GO

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

SELECT db_name (database_id) DBName,

physical_name AS CurrentLocation,

name as LogicalName, state_desc 

FROM sys.master_files 

WHERE database_id=100