MS SQL Server: Detach and Attach Database

Moving User Databases

The following example moves a database that is named mydb. This database contains one data file, mydbdata.mdf, and one log file, mydblog.ldf. If the database being moved 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. Open Transact-SQL (SQL Query Analyzer).

2. Click New Query, and then detach the database as follows:

use master
	 go
	 sp_detach_db 'mydb'
	 go

3. Copy the data files and the log files from their current location to the new location (E:\Sqldata).

4. Reattach 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

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

use mydb
	 go
	 sp_helpfile
	 go

The filename column values should reflect the new locations.

---