主页 / dba / 问题 / 193976
Accepted
Craig Efrein
Asked: 2017-12-27 08:51:05 +0800 CST

为所有现有用户数据库生成 CREATE DATABASE FOR ATTACH

  • 772

有没有人有一个 TSQL 脚本可以使用CREATE DATABASE FOR ATTACH语法自动生成附加所有现有用户数据库所需的代码？

例子：

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

我找到了许多使用 sp_attach_db 的示例，但没有一个使用 CREATE DATABASE FOR ATTACH 语法的示例。

谢谢，

克雷格

sql-server attach

3 个回答

  1. Best Answer

    这个为我完成了这项工作。您可以调整第一个 CTE 以包含或排除特定数据库，然后它将生成适当的命令。 

    ;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;
    • 6

  2. 温馨提醒阅读 -坏习惯：使用 MDF/LDF 文件作为“备份”

    如果您充分意识到并有无法避免的情况，您可以使用 dbatool 的 - Mount-DbaDatabase

    • 1

  3. 你试过这个吗？它工作得很好。

    • -1

相关问题