SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database

http://blog.sqlauthority.com/2007/08/24/sql-server-2005-t-sql-script-to-attach-and-detach-database/

Following script can be used to detach or attach database. If database is to be from one database to another database following script can be used detach from old server and attach to new server.

Process to move database :
----Step 1 : Detach Database using following script
USE [master]
GO
EXEC master.dbo. sp_detach_db @dbname = N'AdventureWorks' ,
@keepfulltextindexfile = N'true'
GO
----Step 2 : Move Data files and Log files to new location

–—Step 3 : Attach Database using following script
USE [master]
GO
CREATE DATABASE [AdventureWorks] ON
( FILENAME = N’C:/Data/AdventureWorks_Data.mdf’ ),
( FILENAME = N’C:/Data/AdventureWorks_Log.ldf’ )
FOR ATTACH
GO
IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = N’AdventureWorks’
AND SUSER_SNAME ( sd.owner_sid ) = SUSER_SNAME () )
EXEC [AdventureWorks].dbo. sp_changedbowner @loginame = N’sa’ ,
@map = false
GO