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

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.

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

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

, , , ,
Previous Post
SQL SERVER – 2005 – Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today
Next Post
SQL SERVER – FIX : Error : msg 2540 – The system cannot self repair this error

Related Posts

32 Comments. Leave new

  • Khondaker haque
    February 16, 2008 3:47 pm

    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?

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Hi,
    This topic is very useful
    Thank’s

    Reply
  • Worked flawlessly, thanks for the script!

    Reply
  • 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.

    Reply
  • Imran Mohammed
    August 24, 2008 11:19 am

    @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.

    Reply
    • I am unable to attached the database copied from one machine to another machine …. please help to resolve it and email at [email removed]
      Regards

      Reply
  • 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?

    Reply
  • Helped me immensely for coming over issues with login after detaching a default DB of windows user. Alter login didn’t worked that time.

    Reply
  • 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.

    Reply
  • Kshitij Biyani
    May 23, 2009 10:21 pm

    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:

    ——————————
    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:

    ——————————
    BUTTONS:

    OK
    ——————————

    Reply
  • Kshitij Biyani
    May 23, 2009 10:23 pm

    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:

    ——————————
    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:

    ——————————
    BUTTONS:

    OK
    ——————————

    Reply
  • Imran Mohammed
    May 24, 2009 4:02 am

    @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.

    Reply
  • 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

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

    Reply
  • Nice code very helpful !!

    thanks !

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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 .

    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.

    Reply
  • 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

    Reply

Leave a Reply

Menu