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

SQL Backup, SQL Data Storage, SQL Restore, SQL Scripts, SQL Server
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

  • Yogesh singhal
    April 25, 2011 3:59 pm

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

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

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

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

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

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

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

    Reply
  • Thnks a lot. Its working fine.

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

    Reply
  • Hi,

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

    Many thanks,

    David

    Reply
  • Hi :-)

    How to detach sql server database using .bat file?

    can you help me?

    Reply

Leave a Reply