Script to attach multiple databases in 1 Click | SQL Server | Migration

Dear All,

Scenario :
Source Server : SQL Server 2008 R2, Windows Server 2008 ,
Destination Server : SQL Server 2014, Windows Server 2012 R2
We are in process of SQL Server Migration from 2008 R2 to 2014

We faced situation that we had to attach 600+ databases to newly created setup, which would require 10+ Hrs.

Then i decided to create script for it.

Thanks to google, I got ready made information from Michelle Gutzait (https://www.mssqltips.com/sqlservertip/1786/auto-generate-sql-server-database-attach-and-detach-scripts/#comments)

And comment from James E. Satterthwaite helped me for my requirement.

Hope that this helps for you too.

================
Solution
================

Goto SQL Server Management Studio on the Source Server and Run this query on the required instance.

-- *******************************************************
-- Script for attaching multiple Databases at a time
-- Do following if you want to relocate DB and log files elsewhere
-- FIND    H:\MSSQL10_50.APP\MSSQL\DATA\    and Replace with     E:\MSSQL12.SQL2014NEW\MSSQL\DATA\
-- FIND     I:\MSSQL10_50.APP\MSSQL\Data\     and Replace with     F:\MSSQL12.SQL2014NEW\MSSQL\Log\
-- *******************************************************
                          
SET NOCOUNT ON
DECLARE     @cmd        VARCHAR(MAX),
            @dbname     VARCHAR(200),
            @prevdbname VARCHAR(200)

SELECT @cmd = '', @dbname = ';', @prevdbname = ''

CREATE TABLE #Attach
    (Seq        INT IDENTITY(1,1) PRIMARY KEY,
     dbname     SYSNAME NULL,
     fileid     INT NULL,
     filename   VARCHAR(1000) NULL,
     TxtAttach  VARCHAR(MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
            WHERE FileID = 1
            AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model'))
ORDER BY DB_NAME(dbid), fileid, filename

UPDATE #Attach
SET @cmd = TxtAttach = 
            CASE WHEN dbname <> @prevdbname
            THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
            ELSE @cmd
            END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
    @dbname = dbname
FROM #Attach  WITH (INDEX(0),TABLOCKX)
 OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
 GROUP BY dbname) AS x

DROP TABLE #Attach
GO 

================
Script End
================