How to bulk detach / attach databases in SQL Server

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

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];
GO
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%'

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    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

WHILE @@FETCH_STATUS = 0 
    BEGIN
        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
        DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
        FOR
            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 
            BEGIN
                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
            END

        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
    END

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:

Sources:

• Database Detach and Attach (SQL Server)