MS SQLServer 批量附加数据库

1 ************************************************************  
2 * 标题：MS SQLServer 批量附加数据库  
3 * 说明：请根据下面的注释使用此脚本  
4 * 时间: 2015/7/13 11:16:41  
5 ************************************************************/  
6  
7USE MASTER  
8GO  
9  
10IF OBJECT_ID('[sp_AttchDataBase]') IS NOT NULL  
11    DROP PROCEDURE [sp_AttchDataBase]  
12GO  
13  
14/*附加数据库(V2.0) Andy 2011-7-8 */  
15CREATE PROCEDURE sp_AttchDataBase(  
16    @Path       NVARCHAR(1024),  
17    @DataFiles  NVARCHAR(MAX) = NULL,  
18    @SplitStr   NVARCHAR(50) = ','  
19)  
20AS  
21    SET NOCOUNT ON  
22      
23    /*  
24    V2.0 版本，在V1.0基础上，处理文件路径不规范原則，e.g. @DataFiles='E:\"my data DB"\"Hello RT"'  
25      
26    @Path       文件路径  
27    @DataFiles  文件名列表  
28    @SplitStr   文件名列表中的文件分隔符  
29      
30    1.必须把要附加的数据库文件(*.mdf和*.ldf)放到@Path下，  
31    2.当@DataFiles Is Null 会附加@Path文件夹下的所有数据库文件.  
32      
33    e.g:  
34    Exec sp_AttchDataBase 'D:\SQL2005\DE2\'  
35    */  
36      
37      
38    --检查文件路径是否正确  
39    DECLARE @Dir  NVARCHAR(1024),  
40            @i    INT,  
41            @x    XML  
42      
43    IF RIGHT(@Path, 1) <> '\'  
44        SET @Path = @Path + '\'  
45      
46    IF CHARINDEX('\\', @Path) > 0  
47    BEGIN  
48        RAISERROR 50001 N'文件路径中不能包含有"\\",@Path设置错误.'  
49        RETURN(1)  
50    END  
51      
52    SET @Dir = 'Dir ' + @Path  
53    EXEC @i = xp_cmdshell @Dir,  
54         no_output  
55      
56    IF @i <> 0  
57    BEGIN  
58        RAISERROR 50001 N'无效的文件路径,@Path设置错误.'  
59        RETURN(1)  
60    END  
61      
62    SET @Path = REPLACE(@Path, '"', '') /*处理文件路径不规范原則*/  
63      
64    DECLARE @Files               TABLE(NAME NVARCHAR(512))  
65    DECLARE @filetmpfin          TABLE(  
66                NAME NVARCHAR(255) NOT NULL,  
67                depth INT NULL,  
68                IsFile BIT NULL  
69            )  
70      
71    DECLARE @SmoPrimayChildren   TABLE(  
72                STATUS INT,  
73                fileid INT,  
74                NAME SYSNAME,  
75                FILENAME NVARCHAR(512)  
76            )  
77      
78    DECLARE @smoPrimaryFileProp  TABLE(PROPERTY SQL_VARIANT NULL, VALUE SQL_VARIANT NULL)  
79      
80    SET @DataFiles = REPLACE(  
81            REPLACE(REPLACE(@DataFiles, CHAR(13) + CHAR(10), ''), CHAR(13), ''),  
82            CHAR(10),  
83            ''  
84        )  
85      
86    SET @x = N'<Root><File>' + REPLACE(@DataFiles, @SplitStr, N'</File><File>') +   
87        N'</File></Root>'  
88      
89      
90    INSERT INTO @Files  
91    SELECT t.v.value('.[1]', 'nvarchar(512)') AS NAME  
92    FROM   @x.nodes('Root/File') t(v)  
93    WHERE  t.v.value('.[1]', 'nvarchar(512)') > ''  
94      
95      
96    INSERT INTO @filetmpfin  
97    EXEC MASTER.dbo.xp_dirtree @Path,  
98         1,  
99         1  
100      
101    DECLARE @File      NVARCHAR(255),  
102            @sql       NVARCHAR(4000),  
103            @DataBase  SYSNAME  
104      
105      
106      
107    DECLARE cur_File   CURSOR    
108    FOR  
109        SELECT NAME  
110        FROM   @filetmpfin AS a  
111        WHERE  IsFile = 1  
112               AND NAME LIKE '%.mdf'  
113               AND (  
114                       EXISTS(  
115                           SELECT 1  
116                           FROM   @Files  
117                           WHERE  NAME = a.Name  
118                       )  
119                       OR @DataFiles IS NULL  
120                   )  
121               AND NOT EXISTS(  
122                       SELECT 1  
123                       FROM   MASTER.sys.master_files  
124                       WHERE  physical_name = @Path + a.Name  
125                   )  
126      
127    OPEN cur_File  
128      
129    BEGIN TRY  
130        FETCH NEXT FROM cur_File INTO @File  
131        WHILE @@Fetch_Status = 0  
132        BEGIN  
133            SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''  
134                ' , 2) With No_Infomsgs'  
135              
136            INSERT INTO @smoPrimaryFileProp  
137            EXEC (@sql)  
138              
139            SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''  
140                ' , 3) With No_Infomsgs'  
141              
142            INSERT INTO @SmoPrimayChildren  
143            EXEC (@sql)  
144              
145            SELECT @DataBase = QUOTENAME(CONVERT(NVARCHAR(255), VALUE)),  
146                   @sql = NULL  
147            FROM   @smoPrimaryFileProp  
148            WHERE  CONVERT(NVARCHAR(255), PROPERTY) = 'Database name'  
149              
150            SELECT @sql = ISNULL(  
151                       @sql + ',' + CHAR(13) + CHAR(10),  
152                       'Create DataBase ' + @DataBase + ' On' + CHAR(13) + CHAR(10)  
153                   ) +  
154                   '(FileName=N''' + @Path + RIGHT(  
155                       RTRIM(FILENAME),  
156                       CHARINDEX('\', REVERSE(RTRIM(FILENAME))) -1  
157                   ) + ''')'  
158            FROM   @SmoPrimayChildren  
159              
160            EXEC (@sql + ' For Attach')  
161              
162            PRINT N'成功附加数据库: ' + @DataBase  
163              
164            DELETE   
165            FROM   @SmoPrimayChildren  
166              
167            DELETE   
168            FROM   @smoPrimaryFileProp  
169              
170            FETCH NEXT FROM cur_File INTO @File  
171        END  
172    END TRY  
173    BEGIN CATCH  
174        DECLARE @Error NVARCHAR(2047)  
175        SET @Error = ERROR_MESSAGE()  
176        RAISERROR 50001 @Error  
177    END CATCH  
178      
179      
180    CLOSE cur_File  
181    DEALLOCATE cur_File  
182GO  
183  
184/************************************************************  
185 * 调用方式  
186 ************************************************************/  
187--use master  
188--Go  
189   
190--Exec sp_AttchDataBase   
191--        @Path = 'E:\100.其他\测试', -- nvarchar(1024)  
192--        @DataFiles = NULL, -- nvarchar(max)  
193--        @SplitStr = NULL -- nvarchar(50)