<nobr id="b3zbt"><big id="b3zbt"><progress id="b3zbt"></progress></big></nobr>

      <big id="b3zbt"></big>

          <sub id="b3zbt"><noframes id="b3zbt">

          <listing id="b3zbt"></listing>
          首頁(yè) 服務(wù) 產(chǎn)品 文檔 關(guān)于

          批量附加數據庫

          /************************************************************??
          ?*?標題:MS?SQLServer?批量附加數據庫??
          ?*?說(shuō)明:請根據下面的注釋使用此腳本??
          ?************************************************************/??
          ??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?會(huì )附加@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'無(wú)效的文件路徑,@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''?+?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'成功附加數據庫:?'?+?@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)
          a级片在线观看免费,欧美精品片在线观看网站,免费观看a级毛片在线播放,成人午夜兔费观看网站

            <nobr id="b3zbt"><big id="b3zbt"><progress id="b3zbt"></progress></big></nobr>

              <big id="b3zbt"></big>

                  <sub id="b3zbt"><noframes id="b3zbt">

                  <listing id="b3zbt"></listing>