MS SQLServer 批次附加資料庫的方法

/************************************************************  
 * 标题：MS SQLServer 批量附加数据库  
 * 说明：请根据下面的注释使用此脚本  
 * 时间: 2015/7/13 11:16:41  
 ************************************************************/  
  USE MASTER  
GO  
  IF OBJECT_ID('[sp_AttchDataBase]') IS NOT NULL  
    DROP PROCEDURE [sp_AttchDataBase]  GO  
  /*附加数据库(V2.0) Andy 2011-7-8 */  CREATE PROCEDURE sp_AttchDataBase(  
    @Path       NVARCHAR(1024),  
    @DataFiles  NVARCHAR(MAX) = NULL,  
    @SplitStr   NVARCHAR(50) = ','  )  
AS  
    SET NOCOUNT ON  
      
    /*  
    V2.0 版本，在V1.0基础上，处理文件路径不规范原則，e.g. @DataFiles='E:\"my data DB"\"Hello RT"'  
      
    @Path       文件路径  
    @DataFiles  文件名列表  
    @SplitStr   文件名列表中的文件分隔符  
      
    1.必须把要附加的数据库文件(*.mdf和*.ldf)放到@Path下，  
    2.当@DataFiles Is Null 会附加@Path文件夹下的所有数据库文件.  
      
    e.g:  
    Exec sp_AttchDataBase 'D:\db2'  
    */  
      
      
    --检查文件路径是否正确  
    DECLARE @Dir  NVARCHAR(1024),  
            @i    INT,  
            @x    XML  
      
    IF RIGHT(@Path, 1) <> &#39;\&#39;  
        SET @Path = @Path + &#39;\&#39;  
      
    IF CHARINDEX(&#39;\\&#39;, @Path) > 0  
    BEGIN  
        --RAISERROR 50001 N&#39;文件路径中不能包含有"\\",@Path设置错误.&#39;  
        RETURN(1)  
    END  
      
    SET @Dir = &#39;Dir &#39; + @Path  
    EXEC @i = xp_cmdshell @Dir,  
         no_output  
      
    IF @i <> 0  
    BEGIN  
        --RAISERROR 50001 N&#39;无效的文件路径,@Path设置错误.&#39;  
        RETURN(1)  
    END  
      
    SET @Path = REPLACE(@Path, &#39;"&#39;, &#39;&#39;) /*处理文件路径不规范原則*/  
      
    DECLARE @Files               TABLE(NAME NVARCHAR(512))  
    DECLARE @filetmpfin          TABLE(  
                NAME NVARCHAR(255) NOT NULL,  
                depth INT NULL,  
                IsFile BIT NULL  
            )  
      
    DECLARE @SmoPrimayChildren   TABLE(  
                STATUS INT,  
                fileid INT,  
                NAME SYSNAME,  
                FILENAME NVARCHAR(512)  
            )  
      
    DECLARE @smoPrimaryFileProp  TABLE(PROPERTY SQL_VARIANT NULL, VALUE SQL_VARIANT NULL)  
      
    SET @DataFiles = REPLACE(  
            REPLACE(REPLACE(@DataFiles, CHAR(13) + CHAR(10), &#39;&#39;), CHAR(13), &#39;&#39;),  
            CHAR(10),  
            &#39;&#39;  
        )  
      
    SET @x = N&#39;<Root><File>&#39; + REPLACE(@DataFiles, @SplitStr, N&#39;</File><File>&#39;) +   
        N&#39;</File></Root>&#39;  
      
      
    INSERT INTO @Files  
    SELECT t.v.value(&#39;.[1]&#39;, &#39;nvarchar(512)&#39;) AS NAME  
    FROM   @x.nodes(&#39;Root/File&#39;) t(v)  
    WHERE  t.v.value(&#39;.[1]&#39;, &#39;nvarchar(512)&#39;) > &#39;&#39;  
      
      
    INSERT INTO @filetmpfin  
    EXEC MASTER.dbo.xp_dirtree @Path,  
         1,  
         1  
      
    DECLARE @File      NVARCHAR(255),  
            @sql       NVARCHAR(4000),  
            @DataBase  SYSNAME  
      
      
      
    DECLARE cur_File   CURSOR    
    FOR  
        SELECT NAME  
        FROM   @filetmpfin AS a  
        WHERE  IsFile = 1  
               AND NAME LIKE &#39;%.mdf&#39;  
               AND (  
                       EXISTS(  
                           SELECT 1  
                           FROM   @Files  
                           WHERE  NAME = a.Name  
                       )  
                       OR @DataFiles IS NULL  
                   )  
               AND NOT EXISTS(  
                       SELECT 1  
                       FROM   MASTER.sys.master_files  
                       WHERE  physical_name = @Path + a.Name  
                   )  
      
    OPEN cur_File  
      
    BEGIN TRY  
        FETCH NEXT FROM cur_File INTO @File  
        WHILE @@Fetch_Status = 0  
        BEGIN  
            SET @sql = &#39;dbcc checkprimaryfile (N&#39;&#39;&#39; + @Path + @File + &#39;&#39;&#39; , 2) With No_Infomsgs&#39;  
              
            INSERT INTO @smoPrimaryFileProp  
            EXEC (@sql)  
              
            SET @sql = &#39;dbcc checkprimaryfile (N&#39;&#39;&#39; + @Path + @File + &#39;&#39;&#39; , 3) With No_Infomsgs&#39;  
              
            INSERT INTO @SmoPrimayChildren  
            EXEC (@sql)  
              
            SELECT @DataBase = QUOTENAME(CONVERT(NVARCHAR(255), VALUE)),  
                   @sql = NULL  
            FROM   @smoPrimaryFileProp  
            WHERE  CONVERT(NVARCHAR(255), PROPERTY) = &#39;Database name&#39;  
              
            SELECT @sql = ISNULL(  
                       @sql + &#39;,&#39; + CHAR(13) + CHAR(10),  
                       &#39;Create DataBase &#39; + @DataBase + &#39; On&#39; + CHAR(13) + CHAR(10)  
                   ) +  
                   &#39;(FileName=N&#39;&#39;&#39; + @Path + RIGHT(  
                       RTRIM(FILENAME),  
                       CHARINDEX(&#39;\&#39;, REVERSE(RTRIM(FILENAME))) -1  
                   ) + &#39;&#39;&#39;)&#39;  
            FROM   @SmoPrimayChildren  
              
            EXEC (@sql + &#39; For Attach&#39;)  
              
            PRINT N&#39;成功附加数据库: &#39; + @DataBase  
              
            DELETE   
            FROM   @SmoPrimayChildren  
              
            DELETE   
            FROM   @smoPrimaryFileProp  
              
            FETCH NEXT FROM cur_File INTO @File  
        END  
    END TRY  
    BEGIN CATCH  
        DECLARE @Error NVARCHAR(2047)  
        SET @Error = ERROR_MESSAGE()  
        --RAISERROR 50001 @Error  
    END CATCH  
      
      
    CLOSE cur_File  
    DEALLOCATE cur_File  
GO  
  /************************************************************  
 * 调用方式  
 ************************************************************/  --use master  --Go  
   --Exec sp_AttchDataBase   --        @Path = &#39;E:\100.其他\测试&#39;, -- nvarchar(1024)  --        @DataFiles = NULL, -- nvarchar(max)  --        @SplitStr = NULL -- nvarchar(50)

 

以上是MS SQLServer 批次附加資料庫的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！