Moving User Database Files

Use the following Script to check the location of your database files, and the logical name of the file:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Database Name Here');
GO

1.) Run the following Transact SQL to change location of your database files

ALTER DATABASE model MODIFY FILE ( NAME = logical name here , FILENAME = 'Type_New_Location_Here\physical database file name here.mdf' );

ALTER DATABASE model MODIFY FILE ( NAME = logical name here , FILENAME = 'Type_New_Location_Here\physical database file name here.ldf' );

2.) Take Database Offline

ALTER DATABASE [Database_Name] SET OFFLINE;

3.) Copy Physical Files to new location
4.) Set Database back online.

ALTER DATABASE [Database_Name] SET ONLINE;

Check new file location for Databases:


SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Database Name Here');
GO

5.) Once you are completely happy that the files are in the correct location, delete them from the old location.