SQL Server database Detach additional operations

The following instance moves a database named MYDBD that contains a data file Mydb.mdf and a log file mydblog.ldf. If the database you are moving has additional data or log files, you can sp_attach_db them all by separating them with commas in the stored procedure.

The following code separates the database

Use master

Go

exec sp_detach_db ' mydb '

Go

Then move the data and log files from the current location (D:\mssql\data) to the new location (E:\sqldata)

Use master

Go

exec sp_attach_db ' mydb ', ' E:\sqldata\mydbdata.mdf ', ' E:\sqldata\mydblog.ldf '

Go

Use sp_helpfile to confirm file location changes

EXEC sp_helpfile

SQL Server database Detach additional operations