Início / dba / Perguntas / 193976
Accepted
Craig Efrein
Asked: 2017-12-27 08:51:05 +0800 CST

Gere CREATE DATABASE FOR ATTACH para todos os bancos de dados de usuários existentes

  • 772

Alguém tem um script TSQL que possa gerar automaticamente o código necessário para anexar TODOS os bancos de dados de usuários existentes usando a sintaxe CREATE DATABASE FOR ATTACH ?

exemplo:

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

Encontrei muitos exemplos usando sp_attach_db, mas nenhum com a sintaxe CREATE DATABASE FOR ATTACH.

Obrigado,

Craig

sql-server attach

3 respostas

  1. Best Answer

    Este faz o trabalho para mim. Você pode ajustar o primeiro CTE para incluir ou excluir um banco de dados específico e ele gerará os comandos apropriados. 

    ;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. Lembrete gentil para ler - Maus Hábitos: Usando Arquivos MDF/LDF como "Backups"

    Se você está totalmente ciente e tem uma situação que não pode evitar, você pode usar dbatool's - Mount-DbaDatabase

    • 1

  3. Você já tentou este ? Funciona muito bem.

    • -1

relate perguntas