服務器和應用系統遷移方案(編輯修改稿)

　

【文章內容簡介】 IP,NOREWIND,NOUNLOAD,STATS=10GO39。print@sql生成的SQLDataBaseName_diffBACKUPDATABASE[DataBaseName]TODISK=N39。D:\DBBackup\39。WITHDIFFERENTIAL,NOFORMAT,NOINIT,NAME=N39。DataBaseName差異數據庫備份39。,SKIP,NOREWIND,NOUNLOAD,STATS=10GO4：差異備份還原declare@dbnamevarchar(100)declare@sqlnvarchar(max)set@dbname=39。DataBaseName39。set@sql=39。RESTORE39。+@dbname+39。_fullRESTOREDATABASE[39。+@dbname+39。]FROMDISK=39。39。D:\DBBackup\39。+@dbname+39。39。39。WITHFILE=1,NOUNLOAD,STATS=10GO39。print@sql生成的SQLRESTOREDataBaseName_fullRESTOREDATABASE[DataBaseName]FROMDISK=39。D:\DBBackup\39。WITHFILE=1,NOUNLOAD,STATS=10GO四、后記以測試的700G的數據文件，我是通過數據庫的作業進行愚公移山的，搬數據到新的服務器上的，這樣的好處就是對之前的數據庫進行優化，比如進行數據庫參數的設置，比如表分區，在對之前數據庫影響盡量小的情況進行數據搬遷。四、遷移重難點分析及措施1.如何保證數據遷移過程中的安全性和操作可審計性？回答：數據遷移中的安全性不可忽略，我們基于多重數據審計功能實現遷移安全性和操作審計性。若要將登錄和密碼從服務器A上的SQLServer實例傳輸到服務器B上的SQLServer實例，請執行以下步驟：1在服務器A上，啟動SQLServerManagementStudio，然后連接到要從中移動數據庫的SQLServer實例。2打開新的查詢編輯器窗口，然后運行以下腳本。USEmasterGOIFOBJECT_ID(39。sp_hexadecimal39。)ISNOTNULLDROPPROCEDUREsp_hexadecimalGOCREATEPROCEDUREsp_hexadecimal@binvaluevarbinary(256),@hexvaluevarchar(514)OUTPUTASDECLARE@charvaluevarchar(514)DECLARE@iintDECLARE@lengthintDECLARE@hexstringchar(16)SELECT@charvalue=39。0x39。SELECT@i=1SELECT@length=DATALENGTH(@binvalue)SELECT@hexstring=39。0123456789ABCDEF39。WHILE(@i=@length)BEGINDECLARE@tempintintDECLARE@firstintintDECLARE@secondintintSELECT@tempint=CONVERT(int,SUBSTRING(@binvalue,@i,1))SELECT@firstint=FLOOR(@tempint/16)SELECT@secondint=@tempint(@firstint*16)SELECT@charvalue=@charvalue+SUBSTRING(@hexstring,@firstint+1,1)+SUBSTRING(@hexstring,@secondint+1,1)SELECT@i=@i+1ENDSELECT@hexvalue=@charvalueGOIFOBJECT_ID(39。sp_help_revlogin39。)ISNOTNULLDROPPROCEDUREsp_help_revloginGOCREATEPROCEDUREsp_help_revlogin@login_namesysname=NULLASDECLARE@namesysnameDECLARE@typevarchar(1)DECLARE@hasaccessintDECLARE@denyloginintDECLARE@is_disabledintDECLARE@PWD_varbinaryvarbinary(256)DECLARE@PWD_stringvarchar(514)DECLARE@SID_varbinaryvarbinary(85)DECLARE@SID_stringvarchar(514)DECLARE@tmpstrvarchar(1024)DECLARE@is_policy_checkedvarchar(3)DECLARE@is_expiration_checkedvarchar(3)DECLARE@defaultdbsysnameIF(@login_nameISNULL)DECLARElogin_cursCURSORFORSELECT,,,,,,FROMpLEFTJOINlON(=)WHEREIN(39。S39。,39。G39。,39。U39。)AND39。sa39。ELSEDECLARElogin_cursCURSORFORSELECT,,,,,,FROMpLEFTJOINlON(=)WHEREIN(39。S39。,39。G39。,39。U39。)AND=@login_nameOPENlogin_cursFETCHNEXTFROMlogin_cursINTO@SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denyloginIF(@@fetch_status=1)BEGINPRINT39。Nologin(s)found.39。CLOSElogin_cursDEALLOCATElogin_cursRETURN1ENDSET@tmpstr=39。/*sp_help_revloginscript39。PRINT@tmpstrSET@tmpstr=39。**Generated39。+CONVERT(varchar,GETDATE())+39。on39。+@@SERVERNAME+39。*/39。PRINT@tmpstrPRINT39。39。WHILE(@@fetch_status1)BEGINIF(@@fetch_status2)BEGINPRINT39。39。SET@tmpstr=39。Login:39。+@namePRINT@tmpstrIF(@typeIN(39。G39。,39。U39。))BEGINNTauthenticatedaccount/groupSET@tmpstr=39。CREATELOGIN39。+QUOTENAME(@name)+39。FROMWINDOWSWITHDEFAULT_DATABASE=[39。+@defaultdb+39。]39。ENDELSEBEGINSQLServerauthenticationobtainpasswordandsidSET@PWD_varbinary=CAST(LOGINPROPERTY(@name,39。PasswordHash39。)ASvarbinary(256))EXECsp_hexadecimal@PWD_varbinary,@PWD_stringOUTEXECsp_hexadecimal@SID_varbinary,@SID_stringOUTobtainpasswordpolicystateSELECT@is_policy_checked=CASEis_policy_checkedWHEN1THEN39。ON39。WHEN0THEN39。OFF39。ELSENULLENDFROMWHEREname=@nameSELECT@is_expiration_checked=CASEis_expiration_checkedWHEN1THEN39。ON39。WHEN0THEN39。OFF39。ELSE