How To: Move MS SQL Database to a New Location

This article describes how to change the location of the data files and of the log files for any Microsoft SQL Server 2005, SQL Server 2000, or SQL Server 7.0 database.

The following example moves a database that is named mydb. This database contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database that you are moving has more data files or log files, specify the files in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change regardless of how many files the database contains because the sp_detach_db procedure does not list the files.

1. Detach the database as follows:

   use master
      go
      sp_detach_db 'mydb'
      go
   

2. Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
3. Re-attach the database. Point to the files in the new location as follows:

   use master
     go  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
     go
   

4. Verify the change in file locations by using the sp_helpfile stored procedure:

   use mydb
      go
      sp_helpfile
      go
   

See this Microsoft Article for more details