/************************************************************??
?*?標(biāo)題：MS?SQLServer?批量附加數(shù)據(jù)庫??
?*?說明：請根據(jù)下面的注釋使用此腳本??
?************************************************************/??
??USE?MASTER?? GO??
??IF?OBJECT_ID('[sp_AttchDataBase]')?IS?NOT?NULL??
????DROP?PROCEDURE?[sp_AttchDataBase]??GO??
??/*附加數(shù)據(jù)庫(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基礎(chǔ)上，處理文件路徑不規(guī)范原則，e.g.?@DataFiles='E:\"my?data?DB"\"Hello?RT"'??
??????
????@Path???????文件路徑??
????@DataFiles??文件名列表??
????@SplitStr???文件名列表中的文件分隔符??
??????
????1.必須把要附加的數(shù)據(jù)庫文件(*.mdf和*.ldf)放到@Path下，??
????2.當(dāng)@DataFiles?Is?Null?會附加@Path文件夾下的所有數(shù)據(jù)庫文件.??
??????
????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設(shè)置錯誤.'?? ????????RETURN(1)??
????END??
??????
????SET?@Dir?=?'Dir?'?+?@Path??
????EXEC?@i?=?xp_cmdshell?@Dir,??
?????????no_output??
??????
????IF?@i?<>?0??
????BEGIN??
????????--RAISERROR?50001?N'無效的文件路徑,@Path設(shè)置錯誤.'?? ????????RETURN(1)??
????END??
??????
????SET?@Path?=?REPLACE(@Path,?'"',?'')?/*處理文件路徑不規(guī)范原則*/??
??????
????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''?+?REPLACE(@DataFiles,?@SplitStr,?N'')?+???
????????N''??
??????
??????
????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'成功附加數(shù)據(jù)庫:?'?+?@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??
??/************************************************************??
?*?調(diào)用方式??
?************************************************************/??--use?master??--Go?? ???--Exec?sp_AttchDataBase???--????????@Path?=?'E:\100.其他\測試',?--?nvarchar(1024)??--????????@DataFiles?=?NULL,?--?nvarchar(max)??--????????@SplitStr?=?NULL?--?nvarchar(50)