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)??''??  ????????SET?@Path?=?@Path?+?''??  ??????  ????IF?CHARINDEX('',?@Path)?&gt;?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)')?&gt;?''??  ??????  ??????  ????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:@.其他測試',?--?nvarchar(1024)??--????????@DataFiles?=?NULL,?--?nvarchar(max)??--????????@SplitStr?=?NULL?--?nvarchar(50)