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

August 24, 2007 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 16 Comments

16 Responses

on February 16, 2008 at 3:47 pm | Reply Khondaker haque

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?
on April 9, 2008 at 8:28 pm | Reply Lyn

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
on May 7, 2008 at 12:37 am | Reply Shamshad

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.
on May 26, 2008 at 10:01 pm | Reply Ali.Net

Hi,
This topic is very useful
Thank’s
on August 7, 2008 at 1:47 am | Reply Danny

Worked flawlessly, thanks for the script!
on August 24, 2008 at 7:00 am | Reply Larry

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.
on August 24, 2008 at 11:19 am | Reply Imran Mohammed

@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.
on September 25, 2008 at 2:16 am | Reply Katie

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?
on December 25, 2008 at 4:33 am | Reply Nag

Helped me immensely for coming over issues with login after detaching a default DB of windows user. Alter login didn’t worked that time.
on February 24, 2009 at 5:40 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database [...]
on April 23, 2009 at 1:09 am | Reply John

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.
on May 23, 2009 at 10:21 pm | Reply Kshitij Biyani

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
——————————
on May 23, 2009 at 10:23 pm | Reply Kshitij Biyani

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
——————————
on May 24, 2009 at 4:02 am | Reply Imran Mohammed

@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.
on June 10, 2009 at 11:11 pm | Reply VS

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
on September 10, 2009 at 6:41 pm | Reply sandrar

Hi! I was surfing and found your blog post… nice! I love your blog. :) Cheers! Sandra. R.