Detach Database operations remove a database from its instance without deleting its files. I preserve a recoverable backup before moving them.
The original AdventureWorks example detached the database, moved its data and log files, and attached them on the destination. Detachment removes the instance registration; it does not delete the database files.
-- Administrator template, not an automated migration.
USE master;
EXEC sys.sp_detach_db @dbname = N'AdventureWorks';
-- Copy every required database file only after detachment succeeds.
-- Run the attach on the intended destination instance.
CREATE DATABASE [AdventureWorks] ON
(FILENAME = N'C:\Data\AdventureWorks_Data.mdf'),
(FILENAME = N'C:\Data\AdventureWorks_Log.ldf')
FOR ATTACH;
-- Review the owner separately. Do not assign sa merely because a sample did so.Check the complete move
Confirm connected users, replication, snapshots, and other detach restrictions. Don’t forcibly disconnect users merely to try the sample. Include secondary data files and any additional required containers, not only one MDF and LDF.
Verify destination service-account permissions, supported version upgrade, available space, encryption keys where applicable, and the trusted origin of the files. A database upgraded to a newer engine can’t simply be attached back to an older one.
Restore the surrounding dependencies
Logins, Agent jobs, and other instance-level dependencies don’t travel with the database files. Map users to the intended logins and review ownership on the destination. The original sp_changedbowner call is deprecated; use a deliberate supported ownership change only when required.
For many migrations, backup and restore keeps the source intact while the destination is validated. A detached read-only database also loses differential-base information on reattachment, so review the backup plan afterward.
Reference: Database detach and attach.
Related reading
Detachment is not a migration checklist, it is one step in moving database files.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
32 Comments. Leave new
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..
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…………….
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
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.
/*
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
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
——————————
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
Thnks a lot. Its working fine.
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
Hi,
I need to find by who and when a database has been detached. Where can I found this information ?
Many thanks,
David
Hi :-)
How to detach sql server database using .bat file?
can you help me?