Move SQL Server Databases Using Detach and Attach
–How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
–Prerequisites
–Note You can determine the name and the current location of all files that a database uses by using the
–Prerequisites
–Note You can determine the name and the current location of all files that a database uses by using the
sp_helpfile stored procedure
use
go
sp_helpfile
go
–Detach the database as follows:
use master
go
sp_detach_db ‘mydb’
go
–Next, copy the data files and the log files from the current location (D:Mssql7Data) to the new location (E:Sqldata).
–Re-attach the database. Point to the files in the new location as follows:
use master
go
sp_attach_db ‘mydb’,’E:Sqldatamydbdata.mdf’,’E:Sqldatamydblog.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.
