Moving SQL Server 2005 databases to a different location

Example of moving SQL Server 2005 databases to a different location.

Detaching the database and moving it to a new location

1. Detach the database using these commands (replace mydb with the relevant database name).
   Enter the commands using Microsoft SQL Server Management Studio – New Query)

   use master go sp_detach_db ‘mydb’ go

2. copy mydb.mdf and mydb.ldf from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to the new location (for example E:\SQLdata)

Attaching the database from its new location

1. Attach the database again using the files in the new location as follows (replace mydb with the relevant database name):

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