批量附加数据库

背景

最近我们在替换生产环境的数据库服务器的时候，因该实例下库比较多，差不多有近200个库，加上维护窗口的时间有限，所以我们有必要写段脚本快速批量附加所有的库，并确保所有的库都附加成功。当前的情况是所有的库名都是唯一且不存在库名相似的情形，如 db_1  和 db_12 不存在这种库名相似的情况。

环境

Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64) 

Mar  2 2016 21:29:16 

Copyright (c) Microsoft Corporation

Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

 

过程

第一步 在旧生产环境上执行，获取所有正式在用的库名信息

为了确保需附加的库名，以及为后续附加成功之后检验核对是否存在数据库文件缺少或未成功附加。提前在旧的生产运行如下代码，并且成功导出结构和数据，再拷贝导出的文件至新的服务器上执行。

---读取源数据库信息 先用从源数据库读取数据库信息
use master
IF OBJECT_ID('sourcetable') IS NOT NULL
    DROP TABLE sourcetable;
SELECT name,
    database_id,
    0 AS okflag
INTO sourcetable
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

 

 

将导出至桌面的wen.sql文件拷至新的服务器上，并在ssms中成功执行。

第二步  生成批量附加代码并执行

默认场景是数据库文件已全部拷贝至新的生产环境，也不存在权限限制访问之类的问题。在新的生产环境执行如下代码

/********
Just for a quick review, xp_dirtree has three parameters:
1 directory - This is the directory you pass when you call the stored procedure; for example 'D:\Backup'.
2 depth  - This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
3 isfile - This will either display files as well as each folder.  The default of 0 will not display any files.
*********/
---读取数据库文件
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
    DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree
(
    id INT IDENTITY(1, 1),
    subdirectory NVARCHAR(512),
    depth INT,
    isfile BIT
);
INSERT #DirectoryTree
(   subdirectory,
    depth,
    isfile
)
EXEC master..xp_dirtree 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA',  --数据库文件存放路径，如有多个路径类似。
    1,
    1;
--SELECT *
--FROM #DirectoryTree;
---生成附加代码
DECLARE @file VARCHAR(MAX);
SET @file
    = 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\'; ---具体数据文件存放的路径
DECLARE @name VARCHAR(500),  --数据库名
    @database_id INT,        --数据库ID
    @temp VARCHAR(MAX);      --存放附加代码
SET @temp = '';
DECLARE c_wen CURSOR FAST_FORWARD
FOR
SELECT name,
    database_id
FROM sourcetable
ORDER BY name;
OPEN c_wen;
FETCH NEXT FROM c_wen
INTO @name,
    @database_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @id INT,        --存放附加文件个数值
        @temp_id INT,       --存放最大附加文件个数值
        @subdirectory VARCHAR(MAX), --待附加的文件名
        @t VARCHAR(MAX);    --存放单个库附加的代码
    SET @t = '';
    SELECT @id = COUNT(1)
    FROM #DirectoryTree
    WHERE subdirectory LIKE '%' + @name + '%';
    SELECT @temp_id = COUNT(1)
    FROM #DirectoryTree
    WHERE subdirectory LIKE '%' + @name + '%';
    SELECT ROW_NUMBER() OVER (ORDER BY subdirectory) id,
        subdirectory
    FROM #DirectoryTree
    WHERE subdirectory LIKE '%' + @name + '%';
    WHILE (@id) >= 1       --存在多个需附加的文件
    BEGIN
        SELECT @subdirectory = subdirectory
        FROM
        (
            SELECT ROW_NUMBER() OVER (ORDER BY subdirectory) id,
                subdirectory
            FROM #DirectoryTree
            WHERE subdirectory LIKE '%' + @name + '%'
        ) a
        WHERE a.id = @id;
        SELECT @t
            = @t + '''' + @file + @subdirectory + ''''
              + CASE
                    WHEN @id > 1 THEN
                        ','
                    ELSE
                        '; ' + CHAR(10) + CHAR(13) + 'GO'
                END + CHAR(10) + CHAR(13);
        SET @id = @id - 1;
    END;
    IF (
           @temp_id = 0    --只有库名，不存在附加文件
       )
    BEGIN
        SELECT @t = '';
    END;
    ELSE
    BEGIN
        SELECT @t
            = 'EXEC sys.sp_attach_db ' + '''' + @name + '''' + ',' + CHAR(10)
              + CHAR(13) + @t;
    END;
    FETCH NEXT FROM c_wen
    INTO @name,
        @database_id;
    SELECT @temp = @temp + @t;
END;
SELECT @temp
FOR XML PATH('');
CLOSE c_wen;
DEALLOCATE c_wen;

 

复制xml文件中批量附加代码在新窗口执行。

 

第三步  验证在新的生产环境执行

附加完毕需验证是否存在失败或遗漏的情况；

use master
--计算原来生产环境的库合计
select count(1) from [dbo].[sourcetable]  
---缺失或失败的库名
select 
     a.name 
from [sourcetable] a  left join 
     sys.databases b 
     on a.name=b.name
where b.name is null

 

参考 

sp_attach_db

后记

有兴趣的同学可以考虑使用PowerShell,如foreach折腾一下。