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

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

Reference : Pinal Dave (http://blog.SQLAuthority.com), SQL SERVER – 2005 Take Off Line or Detach Database

About these ads

35 thoughts on “SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database

  1. 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?

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

  6. 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?

    Like

  7. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

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

    Like

  9. 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
    ——————————

    Like

  10. 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
    ——————————

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

  17. /*
    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

    Like

  18. 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
    ——————————

    Like

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

    Like

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

    Like

  21. Hi,

    I need to find by who and when a database has been detached. Where can I found this information ?

    Many thanks,

    David

    Like

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  23. Pingback: SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s