MS SQLServer 批量附加数据库

来源：互联网发布：2017大数据的发展现状编辑：程序博客网时间：2024/04/18 09:39
/************************************************************   * 标题：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) <> '\'          SET @Path = @Path + '\'            IF CHARINDEX('\\', @Path) > 0      BEGIN          --RAISERROR 50001 N'文件路径中不能包含有"\\",@Path设置错误.'          RETURN(1)      END            SET @Dir = 'Dir ' + @Path      EXEC @i = xp_cmdshell @Dir,           no_output            IF @i <> 0      BEGIN          --RAISERROR 50001 N'无效的文件路径,@Path设置错误.'          RETURN(1)      END            SET @Path = REPLACE(@Path, '"', '') /*处理文件路径不规范原則*/            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), ''), CHAR(13), ''),              CHAR(10),              ''          )            SET @x = N'<Root><File>' + REPLACE(@DataFiles, @SplitStr, N'</File><File>') +           N'</File></Root>'                  INSERT INTO @Files      SELECT t.v.value('.[1]', 'nvarchar(512)') AS NAME      FROM   @x.nodes('Root/File') t(v)      WHERE  t.v.value('.[1]', 'nvarchar(512)') > ''                  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 '%.mdf'                 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 = 'dbcc checkprimaryfile (N''' + @Path + @File + ''' , 2) With No_Infomsgs'                            INSERT INTO @smoPrimaryFileProp              EXEC (@sql)                            SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''' , 3) With No_Infomsgs'                            INSERT INTO @SmoPrimayChildren              EXEC (@sql)                            SELECT @DataBase = QUOTENAME(CONVERT(NVARCHAR(255), VALUE)),                     @sql = NULL              FROM   @smoPrimaryFileProp              WHERE  CONVERT(NVARCHAR(255), PROPERTY) = 'Database name'                            SELECT @sql = ISNULL(                         @sql + ',' + CHAR(13) + CHAR(10),                         'Create DataBase ' + @DataBase + ' On' + CHAR(13) + CHAR(10)                     ) +                     '(FileName=N''' + @Path + RIGHT(                         RTRIM(FILENAME),                         CHARINDEX('\', REVERSE(RTRIM(FILENAME))) -1                     ) + ''')'              FROM   @SmoPrimayChildren                            EXEC (@sql + ' For Attach')                            PRINT N'成功附加数据库: ' + @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 = 'E:\100.其他\测试', -- nvarchar(1024)  --        @DataFiles = NULL, -- nvarchar(max)  --        @SplitStr = NULL -- nvarchar(50)
阅读全文
0 0