MSSQLServer批量附加数据库的方法

/************************************************************ 
 * 标题：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)