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),
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?