批量分离库代码实践

 

背景

在实际的工作中，我们可能会针对生产环境的数据库进行维护，需把一些未用或废弃的库分离。针对这个简单需求，基本思路就是使用sp_detach_db分离当前未使用的数据库，我们整理一下批量分离的代码，供后续可以重用。如需批量附加可以参考我们的《批量附加数据库》。

测试环境

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;
DECLARE @temp NVARCHAR(4000);
SET @temp = '';
DECLARE cursorname CURSOR FORWARD_ONLY
FOR
SELECT name
FROM sys.databases
WHERE database_id > 4
      AND name LIKE 'test_%'
      AND state_desc = 'online'
      AND is_read_only = 0
      AND is_published = 0
      AND name IN ( 'test_1012986', 'test_1012846', 'test_1012852',
                      'test_1012766', 'test_1012744', 'test_1012743',
                      'test_1012601', 'test_1012448', 'test_1012302',
                      'test_1012187', 'test_1012051', 'test_1011929',
                      'test_1011919', 'test_1011880', 'test_1011735',
                      'test_1011734', 'test_1011587', 'test_1011440',
                      'test_1011439', 'test_1011127', 'test_1011061',
                      'test_1011021', 'test_1011006', 'test_1010999',
                      'test_1010916', 'test_1010833', 'test_1010663'          
                  );

OPEN cursorname;
DECLARE @curname NVARCHAR(50);
FETCH NEXT FROM cursorname
INTO @curname;
WHILE (
          @@fetch_status = 0
      )
BEGIN
    ---提前判断一下该库是否有链接，如有则kill对应的库的链接
    IF
    (
        SELECT COUNT(1)
        FROM sys.sysprocesses
        WHERE DB_NAME(dbid) = @curname
    ) >= 1
    BEGIN
        DECLARE @spid VARCHAR(200);
        DECLARE cur_spid CURSOR FORWARD_ONLY
        FOR
        SELECT spid
        FROM sys.sysprocesses
        WHERE DB_NAME(dbid) = @curname;
        OPEN cur_spid;
        FETCH NEXT FROM cur_spid
        INTO @spid;
        WHILE (
                  @@fetch_status = 0
              )
        BEGIN
            EXEC (' kill   ' + @spid);
            -- PRINT (' kill   ' + @spid);
            FETCH NEXT FROM cur_spid
            INTO @spid;
        END;
        CLOSE cur_spid;
        DEALLOCATE cur_spid;
        SET @temp
            = N' EXEC sp_detach_db ' + '''' + @curname + '''' + ', true';
        EXEC (@temp);
    -- PRINT @temp;
    END;
    ELSE
    BEGIN
        SET @temp
            = N' EXEC sp_detach_db ' + '''' + @curname + '''' + ', true';
        EXEC (@temp);    
       -- PRINT @temp;
    END;
    FETCH NEXT FROM cursorname
    INTO @curname;
END;
CLOSE cursorname;
DEALLOCATE cursorname;

--验证代码
SELECT name FROM sys.databases

 

总结

• 注意执行上述脚本的权限的问题；
• 注意在生产环境要先测试验证；
• 上述脚本的逻辑主要是使用的游标实现，未使用sp_MSforeachdb，实际该存储过程也是用游标实现且有些限制，除使用游标外还可以使用while加row_number()来替换；

参考

sp_detach_db