T-SQL to generate script for attaching and detaching all user database in SQL Server

Recently I was working on a database migration activity and we were suppose to migrate more than eight hundred database from one SQL server to another SQL server on a different hardware. Because we had the same naming conversion for the drive letter and folder name, i wanted to script it in order to avoid manual work.

I wrote below script to generate T-SQL for attaching and detaching databases. Just thought to share on web so that somebody can reuse this script. This script will work fine if you have MDF and LDF only and no NDF files. If you have database with NDF files, this script need little modification.


Run below query to generate T-SQL  for Detach, copy results and paste in another query analyse window and run once you generated T-SQL for attach with second script.

SELECT 'EXEC master.dbo.sp_detach_db @dbname = [' + name + ']' FROM SYSDATABASES WHERE DBID > 4

Run below query to generate T-SQL  for Attach, save the result of this query to use as T-SQL for attaching databases on new SQL Server. You may modify your folder location for MDF and LDF, if those are different on new location.  

SELECT  'CREATE DATABASE [' + DB_NAME(S1.database_id) + '] ON ( FILENAME = N''' + S1.physical_name + '''), ( FILENAME = N''' + S2.physical_name + ''') FOR ATTACH'

FROM SYS.MASTER_FILES S1

JOIN SYS.MASTER_FILES S2 ON S1.database_id =S2.database_id AND s2.[type] = 1

WHERE S1.database_id > 4 AND S1.[type] = 0

I hope the above script will work fine!

Ashis Upadhyay