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' )
+ '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'
