服務(wù)器和應(yīng)用系統(tǒng)遷移方案說明(編輯修改稿)
2025-05-31 12:42 本頁面
【文章內(nèi)容簡介】 dbname+39。39。39。WITHDIFFERENTIAL,NOFORMAT,NOINIT,NAME=N39。39。39。+@dbname+39。差異數(shù)據(jù)庫備份39。39。,SKIP,NOREWIND,NOUNLOAD,STATS=10GO39。print@sql生成的SQLDataBaseName_diffBACKUPDATABASE[DataBaseName]TODISK=N39。D:\DBBackup\39。WITHDIFFERENTIAL,NOFORMAT,NOINIT,NAME=N39。DataBaseName差異數(shù)據(jù)庫備份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的數(shù)據(jù)文件，我是通過數(shù)據(jù)庫的作業(yè)進行愚公移山的，搬數(shù)據(jù)到新的服務(wù)器上的，這樣的好處就是對之前的數(shù)據(jù)庫進行優(yōu)化，比如進行數(shù)據(jù)庫參數(shù)的設(shè)置，比如表分區(qū)，在對之前數(shù)據(jù)庫影響盡量小的情況進行數(shù)據(jù)搬遷。四、遷移重難點分析及措施1.如何保證數(shù)據(jù)遷移過程中的安全性和操作可審計性？回答：數(shù)據(jù)遷移中的安全性不可忽略，我們基于多重數(shù)據(jù)審計功能實現(xiàn)遷移安全性和操作審計性。若要將登錄和密碼從服務(wù)器A上的SQLServer實例傳輸?shù)椒?wù)器B上的SQLServer實例，請執(zhí)行以下步驟：1在服務(wù)器A上，啟動SQLServerManagementStudio，然后連接到要從中移動數(shù)據(jù)庫的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=@name
公司管理相關(guān)推薦
鄂ICP備17016276號-1