How to bulk detach / attach databases in SQL Server

How to bulk detach / attach databases in SQL Server
How to bulk detach / attach databases in SQL Server

Sometimes we will need to move databases to SQL Server. This process is also done with backup / restore either by changing the status of the base to offline in the article, however, we will deal with its procedures detach and attach. In the article we will see a script that creates the commands that complete this task.

For what reasons it needs to be done

The usual reason is to we transfer base on a different one instance or even a different server. From there we can move the database files to a separate disk for performance and space reasons. Finally we can do attach a base on a newer version instance upgrading her.

What should we watch out for?

With this process we have some limitations. Let's analyze the most basic ones:

  • After transferring the bases, they should be created logins and connect to the users of each bases because you lose this connection during the transfer.
  • His process attach is only done in an instance of the same version as the one that was done detach or younger
  • If it becomes the base attach in a newer version the compatibility level does not itself change the execution of the command alter database compatibility.
  • When we transfer a base it is not transferred as well jobs with it which are always stored in the system base msdb.

The process

The goal of the script is to generate the commands to do detach and attach. We execute orders detach in the instance with the bases we want to transfer. Once completed we transfer the database files. Finally we connect to instance that we want to attach and execute them attach commands.

Example commands:

EXEC sp_detach_db @dbname = 'db_test' , @skipchecks = 'true'; 
EXEC sp_attach_db @dbname = 'db_test'     ,@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\ddd.mdf'     ,@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\fff_log.ldf' 

The script

Before its execution we will be able to define whether it will be done attach in the same drive as the parameter @Drive_New, if we only want a specific base with the parameter @dbname and finally whether or not we want the system bases with the parameter @whichdb:

USE [master];
DECLARE @database NVARCHAR(200) ,
    @cmd NVARCHAR(1000) ,
    @detach_cmd NVARCHAR(4000) ,
    @attach_cmd NVARCHAR(4000) ,
    @file NVARCHAR(1000) ,
    @i INT ,
	@whichdb INT=0,
	@dbname nvarchar(200)='%%',
	@Drive_Now nvarchar(10)='',
	@Drive_New nvarchar(10)='';
create table #tempattach(attach_script varchar(max));
create table #tempdetach(detach_script varchar(max));

-- set 4 for only userdbs, 0 for all dbs 
set @whichdb  = 4

--if u want to attach db on  new drive with same path uncomment with  the correct drive letters
--set @Drive_Now = 'C:\' set @Drive_New = 'Z:\'

--uncomment if u pick only one DBname
--set @dbname = '%DBA%'

    SELECT  RTRIM(LTRIM([name]))
    FROM    sys.databases
    WHERE   database_id > @whichdb
	and name like @dbname;

OPEN dbname_cur

FETCH NEXT FROM dbname_cur INTO @database

        SELECT  @i = 1;

        SET @attach_cmd = 
            'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
      -- Change skip checks to false if you want to update statistics before you detach.
        SET @detach_cmd =
            'EXEC sp_detach_db @dbname = ''' + @database
            + ''' , @skipchecks = ''true'';' + CHAR(10);

      -- Get a list of files for the database
            SELECT  physical_name
            FROM    sys.master_files
            WHERE   database_id = DB_ID(@database)
            ORDER BY [file_id];

        OPEN dbfiles_cur

        FETCH NEXT FROM dbfiles_cur INTO @file

        WHILE @@FETCH_STATUS = 0 
                SET @attach_cmd = @attach_cmd + '    ,@filename'
                    + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
                    + CHAR(10);
                SET @i = @i + 1;

                FETCH NEXT FROM dbfiles_cur INTO @file

        CLOSE dbfiles_cur;

        DEALLOCATE dbfiles_cur; 
       insert into #tempattach values(REPLACE(@attach_cmd,@Drive_Now,@Drive_New));
	   insert into #tempdetach values(@detach_cmd);

        FETCH NEXT FROM dbname_cur INTO @database

CLOSE dbname_cur;

DEALLOCATE dbname_cur; 

select * from #tempdetach
select *  from #tempattach

drop table #tempattach
drop table #tempdetach

When we execute it, the commands for detach and attach of all bases will be produced:

How to bulk detach / attach databases in SQL Server


Share it

Leave a reply