Detach and Attach All User Databases

Sometimes you are in a scenario where you need to transfer a large number of databases from one server to another. If you decide to perform a detach of all ( or majority) of databases, then the following script can be handy.

Script for Detach Operation

SELECT DISTINCT 'use master;' + Char(10) + 'GO' + Char(10)
                + 'ALTER DATABASE ['
                + CONVERT(VARCHAR(500), Db_name(database_id))
                + '] '
                + 'SET  SINGLE_USER WITH ROLLBACK IMMEDIATE'
                + Char(10) + 'use master;' + Char(10) + 'GO'
                + Char(10)
                + 'EXEC master.dbo.sp_detach_db @dbname = N'
                + ''''
                + CONVERT(VARCHAR(500), Db_name(database_id))
                + '''' + ';' + Char(10) + 'GO' + Char(10)
FROM   master.sys.master_files
WHERE  Db_name(database_id) NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

The script generates the script required for detaching the databases. On the where clause, one can exclude any databases if required. The script needs to be run after setting the "Result to Text" Option in SSMS. "Result to Text" will help us to copy the script generate keeping the line breaks and format intact. "Result to Text" can be set from "Query->Result to->Text"

Script for Attach Operation

The following will generate a script to attach the detached databases. Make sure you run the script on the source server before you detach the databases. Because, once you detach the databases, databases wont be present in the server for the script to generate the attach script

The script makes an important assumption. The script assumes that each database contains only one data file and one log file. If you have databases with more than one data or log file, please handle them manually or separately. Also, the script also assumes that source and destination server will have the same path. If the path of the data and log files are to be different, you may perform a find and replace on the script generated. Like before, set the SSMS result grid to text before running the script to keep the formatting intact.

SELECT DISTINCT 'use master;' + Char(10) + 'GO' + Char(10)
                + 'CREATE DATABASE ['
                + CONVERT(VARCHAR(500), Db_name(x.database_id))
                + ']' + ' ON ' + Char(10) + '( physical_name = N'
                + '''' + x.physical_name + '''' + '),' + Char(10)
                + '( physical_name = N' + '''' + y.physical_name
                + '''' + ')' + Char(10) + ' FOR ATTACH' + Char(10) + 'GO'
FROM   master.sys.master_files x,
       master.sys.master_files y
WHERE  Db_name(x.database_id) NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
       AND x.database_id = y.database_id
       AND x.physical_name LIKE '%mdf'
       AND y.physical_name LIKE '%ldf'