How to bulk detach / attach databases in SQL Server

- How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has in an Oracle Database? - 1 November 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:
