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

16 years ago

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.

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://blog.sqlauthority.com),

SQL Backup, SQL Data Storage, SQL Restore, SQL Scripts, SQL Server

SQL SERVER – 2005 – Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today

SQL SERVER – FIX : Error : msg 2540 – The system cannot self repair this error

32 Comments. Leave new

Yogesh singhal
April 25, 2011 3:59 pm
Hello ALL.
I am new to SQL.
I got a Database copied from my SQL 2005 data folder and tried to attach it in 2008 R2..but it is showing errior
“the DB you r trying to attach is not a Primary database File”
While this Database gets easily attached in another SQL servr 2005 edition..
Reply
virendra
November 18, 2011 6:31 pm
Hi Pinal ,
How can lock my database if any user stole my mdf and ldf file,User not able to attach the file.
Thanks
Mayank…………….
Reply
SQL_Help
January 11, 2012 2:47 pm
Made a small script for automatically create the Detach as well as the Attach, useful for server with many Db´s installed
just now it is separeted in detach and attach sections.
Needs to be run before detach shall be made,
makes a Print result but it is easy to change for a execution like this EXEC sp_EXECutesql @cmd.
DECLARE @database_name varchar(128);
DECLARE @file_id varchar(1);
DECLARE @file_name varchar(128);
DECLARE @M_DB varchar(128);
DECLARE databases CURSOR FOR
select fileid,db_name(dbid),filename from sysaltfiles where dbid > ‘4’ and dbid < '32767'
OPEN databases;
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
Print ''
print '************** Statement for Detach ***************'
print ''
WHILE @@FETCH_STATUS = 0
Begin
declare @cmd nvarchar(512)
if @file_id = '1'
begin
select @cmd = 'USE [MASTER] ' +
'ALTER DATABASE [' + @database_name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' +
'EXEC dbo.sp_detach_db N' + CHAR(39) + @database_name + CHAR(39)
print(@cmd)
end
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
end
CLOSE databases;
OPEN databases;
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
Print ''
print '************** Statement for Attach ***************'
Print ''
WHILE @@FETCH_STATUS = 0
Begin
declare @cmd2 nvarchar(512)
select @cmd2 = 'CREATE DATABASE ' + @database_name + ' ON'
if @file_id = '1'
begin
select @cmd2 = @cmd2 + '( FILENAME = N' + CHAR(39) + @file_name + CHAR(39) + '),'
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
end
select @cmd2 = @cmd2 + '( FILENAME = N' + CHAR(39) + @file_name + CHAR(39) + ') FOR ATTACH'
print(@cmd2)
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
end
CLOSE databases;
DEALLOCATE databases
Reply
- AndreQ
  July 15, 2013 6:19 am
  Keep in mind mulitple MDF and NDF files. Also your declare @cmd2 is inside the WHILE so you’ll get an error pretty quickly. The basic idea should be sound though.
  Rather than detaching in bulk, I hope to detach one, copy the files with a powershell script, knowing that it will be done when the file sizes are the same and re-attach them using a variation on your script, with a bunch of validation scripts afterward.
  Reply
SQL_Help
January 11, 2012 7:46 pm
/*
Rewritten, takes several files now
*/
DECLARE @database_name varchar(128);
DECLARE @file_id varchar(3);
DECLARE @old_file_id varchar(3);
DECLARE @file_name varchar(128);
DECLARE @M_DB varchar(128);
DECLARE databases CURSOR FOR
select fileid,db_name(dbid) as databas, filename from sysaltfiles where dbid > ‘4’ and dbid < '32767'
OPEN databases;
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
Print ''
print '************** Statement for Detach ***************'
print ''
WHILE @@FETCH_STATUS = 0
Begin
declare @cmd nvarchar(512)
if @file_id = '1'
begin
select @cmd = 'USE [MASTER] ' +
'ALTER DATABASE [' + @database_name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' +
'EXEC dbo.sp_detach_db N' + CHAR(39) + @database_name + CHAR(39) +
', @keepfulltextindexfile = N' + CHAR(39) + 'true' +CHAR(39)
print(@cmd)
end
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
end
CLOSE databases;
OPEN databases;
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
Print ''
print '************** Statement for Attach ***************'
Print ''
WHILE @@FETCH_STATUS = 0
Begin
declare @cmd2 nvarchar(2048)
select @cmd2 = 'CREATE DATABASE ' + @database_name + ' ON'
if @file_id = '1'
begin
select @cmd2 = @cmd2 + '(FILENAME = N' + CHAR(39) + @file_name + CHAR(39) + ')'
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
set @old_file_id = '1'
Loop_label:
if @file_id = '1' or @file_id = @old_file_id GOTO Done_Label
select @cmd2 = @cmd2 + ',(FILENAME = N' + CHAR(39) + @file_name + CHAR(39) + ')'
set @old_file_id = @file_id
FETCH NEXT FROM databases INTO @file_id,@database_name,@file_name
goto Loop_label
Done_Label:
end
select @cmd2 = @cmd2 + ' FOR ATTACH'
print(@cmd2)
end
CLOSE databases;
DEALLOCATE databases
Reply
gowtham
February 6, 2012 11:10 pm
I TRIED TO NEW DATA BASE BUT I GET THE FOLLWING ERROR PLEASE HELP ME TO CORRECT
TITLE: Microsoft SQL Server Management Studio Express
——————————
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
For help, click:
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
——————————
The server principal “dell-PCdell” is not able to access the database “model” under the current security context. (Microsoft SQL Server, Error: 916)
For help, click:
——————————
BUTTONS:
OK
——————————
Reply
smzuber
March 6, 2012 9:09 am
hi all
am getting “Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – The pipe has been ended.)
” and server automatically get off how to solve this
Reply
Nadeem
December 20, 2012 1:42 pm
Thnks a lot. Its working fine.
Reply
Robert C.
May 6, 2013 8:57 pm
Hi,
Where are stored the log information when a databases is Detached or attached. I need to retrieve by who and when a database has been detached.
Many thanks,
David
Reply
Robert C.
May 6, 2013 8:58 pm
Hi,
I need to find by who and when a database has been detached. Where can I found this information ?
Many thanks,
David
Reply
May-Ann
October 3, 2014 7:54 am
Hi :-)
How to detach sql server database using .bat file?
can you help me?
Reply