SQL-Server-migration(SQL-2000-升级至SQL-2014

SQLServermigration20141.Detach/BackupSQLServer2000Database&exportSQLServer2000UserAccounts1.1Detach/BackupSQLServer2000Database1.2ExportUserAccountfromMasterInstance1.3ExportUserlistfromManagementStudio2.PreparationinstallSQLServer2008/2008R2/2012/20142.1InstallDotNet3.52.2TurnoffWindowsfirewall3.InstallandconfigurationtheinstanceofSQLServer2008/2008R23.1InstallSQLServer2008/2008R23.2TestingSQLServer2008/2008R2services3.3ChangeServerAuthorizationandactive“sa”account4.Attach/BackupdatabaseontoaninstanceofSQLServer2005,2008or2008R24.1AttachDatabaseonSQLServer2005,2008or2008R24.2BackupDatabaseonSQLServer2005,2008or2008R25.InstallandconfigurationtheinstanceofSQLServer20125.1InstallSQLServer20125.2TestingSQLServer2012Services5.3ChangeServerAuthorizationandactive“sa”account5.4Create6.RestoreandBackupDatabaseontoaninstanceofSQLServer20126.1RestoreDatabaseonSQLServer20126.2BackupDatabaseonSQLServer20127.InstallandconfigurationtheinstanceofSQLServer20148.1InstallSQLServer20128.2TestingSQLServer2012Services8.3ChangeServerAuthorizationandactive“sa”account8.RestoredatabaseontoaninstanceofSQLServer20149.ImportSQLServer2000MasterUserAccountsinformationSQLServermigration2014AsMicrosoftisonlysupportingrestoringbackupsfromtheprevious3versions(SQLServer2005,2008and2008R2)withSQLServer2012.Thismeansalotofdatabasemigration,andastheywanttokeeptheapplicationsupwhiletheytestthenewversionsthismeansusingbackupandrestore.Inthiscase,SQLMigrationstartcannotdirectfromSQL2000toSQL2012/2014,soweneedhaveanotherSQLversiontoupgradestepbystep.SQL2000SQL2005/2008/2008R2SQL2012SQL20141.Detach/BackupSQLServer2000DatabaseandexportUserAccount1.1Detach/BackupSQLServerDatabase-OpenEnterprisesManager,rightclick“Alltasks”and“DetachDatabase”-CopyDetach/BackupdatabasefromSQLServer2000toSQLServer2005/2008/2008R21.2ExportUserAccountfromMasterInstance-LoginVisualStudioManagementandconnecttoSQL2000Server,click“NewQuery”-ExecuteScriptandcreatesp_help_revloginprocedurein“Master”instanceUSEmasterGOIFOBJECT_ID('sp_hexadecimal')ISNOTNULLDROPPROCEDUREsp_hexadecimalGOCREATEPROCEDUREsp_hexadecimal@binvaluevarbinary(256),@hexvaluevarchar(256)OUTPUTASDECLARE@charvaluevarchar(256)DECLARE@iintDECLARE@lengthintDECLARE@hexstringchar(16)SELECT@charvalue='0x'SELECT@i=1SELECT@length=DATALENGTH(@binvalue)SELECT@hexstring='0123456789ABCDEF'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('sp_help_revlogin')ISNOTNULLDROPPROCEDUREsp_help_revloginGOCREATEPROCEDUREsp_help_revlogin@login_namesysname=NULLASDECLARE@namesysnameDECLARE@xstatusintDECLARE@binpwdvarbinary(256)DECLARE@txtpwdsysnameDECLARE@tmpstrvarchar(256)DECLARE@SID_varbinaryvarbinary(85)DECLARE@SID_stringvarchar(256)IF(@login_nameISNULL)DECLARElogin_cursCURSORFORSELECTsid,name,xstatus,passwordFROMmaster..sysxloginsWHEREsrvidISNULLANDname'sa'ELSEDECLARElogin_cursCURSORFORSELECTsid,name,xstatus,passwordFROMmaster..sysxloginsWHEREsrvidISNULLANDname=@login_nameOPENlogin_cursFETCHNEXTFROMlogin_cursINTO@SID_varbinary,@name,@xstatus,@binpwdIF(@@fetch_status=-1)BEGINPRINT'Nologin(s)found.'CLOSElogin_cursDEALLOCATElogin_cursRETURN-1ENDSET@tmpstr='/*sp_help_revloginscript'PRINT@tmpstrSET@tmpstr='**Generated'+CONVERT(varchar,GETDATE())+'on'+@@SERVERNAME+'*/'PRINT@tmpstrPRINT''PRINT'DECLARE@pwdsysname'WHILE(@@fetch_status-1)BEGINIF(@@fetch_status-2)BEGINPRINT''SET@tmpstr='--Login:'+@namePRINT@tmpstrIF(@xstatus&4)=4BEGIN--NTauthenticatedaccount/groupIF(@xstatus&1)=1BEGIN--NTloginisdeniedaccessSET@tmpstr='EXECmaster..sp_denylogin'''+@name+''''PRINT@tmpstrENDELSEBEGIN--NTloginhasaccessSET@tmpstr='EXECmaster..sp_grantlogin'''+@name+''''PRINT@tmpstrENDENDELSEBEGIN--SQLServerauthenticationIF(@binpwdISNOTNULL)BEGIN--Non-nullpasswordEXECsp_hexadecimal@binpwd,@txtpwdOUTIF(@xstatus&2048)=2048SET@tmpstr='SET@pwd=CONVERT(varchar(256),'+@txtpwd+')'ELSESET@tmpstr='SET@pwd=CONVERT(varbinary(256),'+@txtpwd+')'PRINT@tmpstrEXECsp_hexadecimal@SID_varbinary,@SID_stringOUTSET@tmpstr='EXECmaster..sp_addlogin'''+@name+''',@pwd,@sid='+@SID_string+',@encryptopt='ENDELSEBEGIN--NullpasswordEXECsp_hexadecimal@SID_varbinary,@SID_stringOUTSET@tmpstr='EXECmaster..sp_addlogin'''+@name+''',NULL,@sid='+@SID_string+',@encryptopt='ENDIF(@xstatus&2048)=2048--loginupgradedfrom6.5SET@tmpstr=@tmpstr+'''skip_encryption_old'''ELSESET@tmpstr=@tmpstr+'''skip_encryption'''PRINT@tmpstrENDENDFETCHNEXTFROMlogin_cursINTO@SID_varbinary,@name,@xstatus,@binpwdENDCLOSElogin_cursDEALLOCATElogin_cursRETURN0GOCommand(s)completedsuccessfully.-Then“Execute”thescripttoexporttheuseraccountinfoin“Master”instance;EXECmaster..sp_help_revloginResultSAMPLE/*sp_help_revloginscript**GeneratedMar31201511:18AMonOCEAN\SQL2000*/DECLARE@pwdsysname--Login:ACC1AXCSET@pwd=CONVERT(varbin