SQL Server : Move database files to another location

There may be situations where you need to move database files from one drive to another. You can move database files of a non-system database as follows:

  1. Right click on the database and select Tasks –> Take Offline
  2. Once the database is offline, detach the database by right click on the database, select Tasks –> Detach
  3. Copy the data and log files to the new location.
  4. Right click on the Databases folder and select Tasks –> Attach
  5. In the Attach Databases window click on Add button and browse and select the data file from the new location and click OK

Follow the steps below to move TempDB to a new directory:

  1. Run the query below with the new location

    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TempDB\tempdb.mdf');
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TempDB\templog.ldf');
    GO
  2. Restart the SQL Server
  3. SQL Server will then create tempDB files in the new location
  4. Delete the old files

You can check the location of the database files with the query below:


SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO