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
Reference : Pinal Dave (http://blog.SQLAuthority.com), SQL SERVER – 2005 Take Off Line or Detach Database
I backed up the database ‘City’ and user logins from one server and restore the database and user logins by another server. Everything was fine. I refreshed the database, but I still can’t login the database. How can I trouble-shoot the problem?
Hi Pinal,
I noticed that when a database is re-attached, it’s creation_date is updated to the date of attachment. Is there any way to change this?
Thanks
Lyn
Attach db query not working at all !!!
Do i have to create a new db and then run this attach query?
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorks’. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 19, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Hi,
This topic is very useful
Thank’s
Worked flawlessly, thanks for the script!
Apparently no one in this uinverse knows how to do this. On the same server, yes it work, but move to other server and you get: Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorks’. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 19, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
@Larry,
Chill Man !!!
I guess, this is what you did ?
1. You executed detached database statement ( sp_detach_db) on server1 and then
2. You logged in to Server2 ( different machine) and
3. There you are trying to attach the database by executing this statement ( sp_attach_db).
But my dear, you have also have to manually move those .mdf and .ldf files from old server to new server.
And while using sp_attach_db stored procedures, give the new location of these files ( where ever you moved it to, ex: D:\program files\ microsoft sql server\………)
Once you move ( copy those .mdf and .ldf files)to new server then use sp_attach_db with the new location ( on new server).
You should be able to attach it.
If you cannot do it through sql commands, let me know I will try to post screen shots for the same using interfaces.
Hope this helps,
Imran.
I used sp_attach like this and get the Msg 1813:
USE [master]
GO
sp_attach_db @dbname = N’TestDB’,
@FILENAME1 = N’C:\Data\TestDB.mdf’ ,
@FILENAME2 = N’C:\Data\TestDB_1.ldf’
What did I miss?
Helped me immensely for coming over issues with login after detaching a default DB of windows user. Alter login didn’t worked that time.
[...] SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database [...]
Pinal – this is *exactly* what I needed and was looking for – always find great SQL tips and code on your site, thanks for all you do.
Hi
I am trying to attach my database to my pc which I made in my trainning but an error is comming Kindly Help :-
TITLE: Microsoft SQL Server Management Studio Express
——————————
Attach database failed for Server ‘KPS-980B2490DB6′. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
——————————
Could not find row in sysindexes for database ID 14, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database ‘db_Swapan’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
——————————
BUTTONS:
OK
——————————
Kindly Help me to sort this error which is comming while attaching my database
TITLE: Microsoft SQL Server Management Studio Express
——————————
Attach database failed for Server ‘KPS-980B2490DB6′. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
——————————
Could not find row in sysindexes for database ID 14, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database ‘db_Swapan’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
——————————
BUTTONS:
OK
——————————
@Biyani,
Please post your question once.
Reponse to your question :
Are you trying to attach SQL Server 2005 database files on SQL Server 2000 ? If that is the case then you cannot attach those files.
Please provide, version details you are trying to restore On.
~IM.
I can find no information on @keepfulltextindexfile parameter for dbcc detachdb. I want to see what happens when this is set to false. I want to be sure that all ft indexing is removed so there will be no confusion with the newly attached db when I rebuild the ft catalog.
Any info you can provide will be immensely helpful.
vs
Hi! I was surfing and found your blog post… nice! I love your blog. :) Cheers! Sandra. R.
Nice code very helpful !!
thanks !
I ran into similar problem when I was working with ASP.NET Dynamic Data Website. When I tried to attach database into App_Data folder in Visual Studio, I used to get an error “The file cannot be opened as it is used by another program.Please close all applications that might access this file and try again.”
After lot of research I realized that my database file was attached to SQL Server and in order to add it to Visual studio, I had to detach the database file from SQL Server.
I ran the script to detach the database file as described in this article :
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = ” @keepfulltextindexfile = ‘true’
GO
And Whoaaaa … I was able to add database file to App_Data folder in Visual Studio!!!
Thank you.
Can you help me to attach databases in bulk, i.e. if there are 100 databases and I’m needed to migrate in bulk to other server through script?
Thank you
Can you help me to attach databases in bulk, i.e. if there are 100 databases and I’m needed to migrate in bulk to other server through script?
Currently I hosted databases on SQL Server 2005 SP2 x64 bit. I’m planning to upgrade my database server to SQL Server 2008 R2 x64, and it’s not supported to upgrade SQL Server 2008 R2 X64 from SQL Server 2005 x64 SP2 (according to http://msdn.microsoft.com/en-us/library/ms143393.aspx).
what is your suggestion?
I am planning to install new version of SQL Server 2008 Rs x64 and migrate all databases to new instance, that is why I am looking for script for attaching SQL Server in bulk.
Thank you.
Hi…
I detached my database and then it does not exist on my computer.Is it deleted on detach? How can I access to my detached file?
Thanks
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
/*
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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
——————————
The server principal “dell-PC\dell” is not able to access the database “model” under the current security context. (Microsoft SQL Server, Error: 916)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
——————————
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