Here are a couple of ways to move the database files to a different location other than the default.
1. Using the SQL Server Management Console, right-click on the database, go to Task and Detach. You may also have to restrict access to the database to SINGLE_USER before you can perform the Detach if there is a process currently using it. To set it to SINGLE_USER mode, right-click on the database, properties, Options and scroll down to Restrict Access.
2. Once the database is detached, copy/move the data file (.MDF) and log file (.LDF) to the new location, and then Attach the database back. Right-click on Databases, select Attach. Hit Add on the Attach Databases window and then browse to the new data file (.MDF) location and select the database. If the log file (.LDF) is located on a separate disk, then you will also have to specify the path for the log file under the Database details window. Then hit OK.
Using T-SQL scripts with Detach:
1. You can run the following scripts to Detach, copy/move the data files and then perform the Attach command. I will use the TEST database with an example path.
ALTER DATABASE TEST
WITH ROLLBACK IMMEDIATE;
EXEC MASTER.DBO.SP_DETACH_DB @dbname = N’TEST‘
CREATE DATABASE [TEST] ON
( FILENAME = N’Y:\DATABASE\TEST.mdf‘ ),
( FILENAME = N’Z:\LOG\TEST_log.ldf‘ )
Using T-SQL Scripts with Alter Command:
ALTER DATABASE [TEST] SET OFFLINE
WITH ROLLBACK IMMEDIATE
— Please note that the EXEC XP_CMDSHELL command might not work if it is not enabled in your SQL server. You will need to enable it first or you can substitute this command with manually copying/moving the data files.
EXEC XP_CMDSHELL ‘COPY “C:\OLD_LOCATION\TEST.mdf” “Y:\DATABASE\TEST.mdf“‘
EXEC XP_CMDSHELL ‘COPY “C:\OLD_LOCATION\TEST_LOG.ldf” “Z:\LOG\TEST_log.ldf“‘
ALTER DATABASE [TEST] MODIFY FILE (NAME = ‘TEST‘, FILENAME = ‘Y:\DATABASE\TEST.mdf“)
ALTER DATABASE [TEST] MODIFY FILE (NAME = ‘TEST_log‘, FILENAME = ‘Z:\LOG\TEST_log.ldf‘)