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

August 24, 2007

Following script can be used to detach or attach the database. If the database is to be from one database to another database following script can be used to detach from old server and attach to a new server. Let us learn about how to Attach and Detach Database.

SQL SERVER - T-SQL Script to Attach and Detach Database attach

Process to move the 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
-- Do this step and move to Step 3
-- 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

SQL SERVER – Take Off Line or Detach Database

Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. It is recommend that you do not attach or restore databases from unknown or untrusted sources. When a read-only database is detached and then reattached, the backup information about the current differential base is lost. When you attach a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata in the database, such as logins and jobs, on the other server instance.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com),