Generate CREATE DATABASE FOR ATTACH for all existing user databases

Question

Does anyone have a TSQL script that can automatically generate the code necessary to attach ALL existing user databases using the CREATE DATABASE FOR ATTACH syntax?

example:

CREATE DATABASE [mydatabase] ON 
(FILENAME=N'E:\MSSQL\Data\mydatabase.mdf'),
(FILENAME=N'D:\MSSQL.1\MSSQL\Data\mydatabase_log.ldf'),
(FILENAME=N'E:\MSSQL\Data\mydatabase_ndf.ndf')
 FOR ATTACH

I have found many examples using sp_attach_db, but none with the CREATE DATABASE FOR ATTACH syntax.

Thanks,

Craig

score 5 · Accepted Answer · answered Dec 26 '17 at 17:12

This one does the job for me. You can tweak the first CTE to include or exclude specific database and it will then generate the appropriate commands.

;WITH CTE_Databases AS
    (
    SELECT D.name AS DatabaseName
        , D.database_id
    FROM sys.databases AS D
    WHERE D.name <> 'tempdb'
        AND NOT(D.name IN ('master', 'msdb', 'model'))
    )
, CTE_FirstAndLast AS
    (
    SELECT 1 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'IF NOT EXISTS (SELECT TOP 1 1 FROM sys.databases WHERE name = ' + QUOTENAME(D.DatabaseName, '''') + ')' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 2 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'BEGIN' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 3 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , ' CREATE DATABASE ' + QUOTENAME(D.DatabaseName) + ' ON ' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 20 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '     FOR ATTACH;' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 90 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'END' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 91 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'GO' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 92 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '' AS CommandText
    FROM CTE_Databases AS D
    )
, CTE_Files AS
    (
    SELECT 10 AS CommandStep
        , ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) AS SubStep
        , F.database_id AS DatabaseID 
        , D.DatabaseName 
        , CASE WHEN ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) > 1 THEN '        , ' ELSE '      ' END + '(FILENAME = N' + QUOTENAME(F.physical_name, '''') + ')' AS CommandText
    FROM sys.master_files AS F
        INNER JOIN CTE_Databases AS D ON d.database_id = F.database_id 
    )
, CTE_AllTogether AS 
    (
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_FirstAndLast
    UNION ALL
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_Files
    )
SELECT * FROM CTE_AllTogether
ORDER BY DatabaseID, DatabaseName, CommandStep, SubStep;

score 1 · Answer 2 · answered Dec 26 '17 at 18:34

1

Gentle reminder to read - Bad Habits : Using MDF/LDF Files as "Backups"

If you are fully aware and have a situation that you cannot avoid, you can use dbatool's - Mount-DbaDatabase

answered Dec 26 '17 at 18:34

Kin Shah

62,545
6
124
245

score -1 · Answer 3 · edited Dec 26 '17 at 17:38

-1

Have you tried this one? It works quite well.

edited Dec 26 '17 at 17:38

Erik Reasonable Rates Darling

45,549
14
145
532

answered Dec 26 '17 at 17:05

rvsc48

553
4
13

Generate CREATE DATABASE FOR ATTACH for all existing user databases

3 Answers3