SQL Server鏡像自動生成腳本

?

鏡像的搭建非常繁瑣，花了一點時間寫了這個腳本，方便大家搭建鏡像

執行完這個鏡像腳本之后，最好在每臺機器都綁定一下hosts文件，不然的話，鏡像可能會不work

192.168.1.1 WSQL01
192.168.1.2 WSQL02
192.168.1.3 WWEB03

?

SQL2008R2升級到SQL2014，升級之前先對數據庫進行完整和日志備份，以免升級失敗

?

請注意：--★Do部分都是需要填寫的

-- ============================================= -- Author: <樺仔> -- Blog: <http://www.cnblogs.com/lyhabc/> -- Create date: <2013/8/18> -- Description: <鏡像自動生成腳本> -- =============================================--環境：非域環境DECLARE @DBName NVARCHAR(255) DECLARE @masterip NVARCHAR(255) DECLARE @mirrorip NVARCHAR(255) DECLARE @witness NVARCHAR(255) DECLARE @masteriptail NVARCHAR(255) DECLARE @mirroriptail NVARCHAR(255) DECLARE @witnesstail NVARCHAR(255) DECLARE @certpath NVARCHAR(MAX) DECLARE @Restorepath NVARCHAR(MAX) DECLARE @Restorepath1 NVARCHAR(MAX) DECLARE @Restorepath2 NVARCHAR(MAX) DECLARE @MKPASSWORD NVARCHAR(500) DECLARE @LOGINPWD NVARCHAR(500) DECLARE @LISTENER_PORT NVARCHAR(500) DECLARE @SQL NVARCHAR(MAX) DECLARE @MASTERHOST_NAME NVARCHAR(50) DECLARE @SLAVEHOST_NAME NVARCHAR(50) DECLARE @WITNESSHOST_NAME NVARCHAR(50)if OBJECT_ID ('tempdb..#temp')is not null BEGIN DROP TABLE #BackupFileList ENDCREATE TABLE #BackupFileList (LogicalName NVARCHAR(100) ,PhysicalName NVARCHAR(100) ,BackupType CHAR(1) ,FileGroupName NVARCHAR(50) ,SIZE BIGINT ,MaxSize BIGINT ,FileID BIGINT ,CreateLSN BIGINT ,DropLSN BIGINT NULL ,UniqueID UNIQUEIDENTIFIER ,ReadOnlyLSN BIGINT NULL ,ReadWriteLSN BIGINT NULL ,BackupSizeInBytes BIGINT ,SourceBlockSize INT ,FileGroupID INT ,LogGroupGUID UNIQUEIDENTIFIER NULL ,DifferentialBaseLSN BIGINT NULL ,DifferentialBaseGUID UNIQUEIDENTIFIER ,IsReadOnly BIT ,IsPresent BIT ,TDEThumbprint NVARCHAR(100))SET NOCOUNT ONSET @masterip='172.16.198.254' --★Do SET @mirrorip='172.16.198.253' --★Do SET @witness='999999' --★Do --目錄后面不要帶分隔符： \ SET @certpath='D:\DBBackup' --★Do SET @Restorepath='D:\DBBackup' --★Do SET @DBName='testmirror' --★Do SET @MKPASSWORD='master@2015key123' --★Do SET @LOGINPWD='User_Pass@2015key123' --★Do SET @LISTENER_PORT='5022' --★Do SET @MASTERHOST_NAME='A' --★Do SET @SLAVEHOST_NAME='B' --★Do SET @WITNESSHOST_NAME='C' --★Doselect @masteriptail= PARSENAME(@masterip,2)+'_'+PARSENAME(@masterip,1) select @mirroriptail= PARSENAME(@mirrorip,2)+'_'+PARSENAME(@mirrorip,1) select @witnesstail= PARSENAME(@witness,2)+'_'+PARSENAME(@witness,1) -------------------------------------------------------------------------------- DECLARE @stat NVARCHAR(MAX)SET @stat='--自動生成鏡像腳本V1 By huazai' PRINT @stat PRINT CHAR(13)+CHAR(13)SET @stat='--0、首先確定要做鏡像的庫的恢復模式為完整，用以下sql語句來查看'+CHAR(13) +'--主機'+CHAR(13) +'SELECT [name], [recovery_model_desc] FROM sys.[databases]'+CHAR(13)+CHAR(13)+CHAR(13)PRINT '--主：'+@masterip PRINT '--備：'+@mirrorip PRINT '--見證：'+@witness PRINT CHAR(13)+CHAR(13) PRINT @stat-------------------------------------------------------------------- PRINT '-- ============================================='SET @stat='--1、 在主服務器和鏡像服務器上和見證服務器上創建Master Key 、創建證書 '+CHAR(13) +'--主機'+CHAR(13) +'USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''+@MKPASSWORD+''';' +'CREATE CERTIFICATE HOST_' +@masteriptail +'_cert WITH SUBJECT = ''HOST_' +@masteriptail +'_certificate'','+CHAR(13) +'START_DATE = ''09/20/2010'',EXPIRY_DATE = ''01/01/2099'';'+CHAR(13)PRINT @statSET @stat='--備機'+CHAR(13) +'USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''+@MKPASSWORD+''';' +'CREATE CERTIFICATE HOST_' +@mirroriptail +'_cert WITH SUBJECT = ''HOST_' +@mirroriptail +'_certificate'','+CHAR(13) +'START_DATE = ''09/20/2010'',EXPIRY_DATE = ''01/01/2099'';'+CHAR(13)PRINT @statSET @stat='--見證'+CHAR(13) +'USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''+@MKPASSWORD+''';' +'CREATE CERTIFICATE HOST_' +@witnesstail +'_cert WITH SUBJECT = ''HOST_' +@witnesstail +'_certificate'','+CHAR(13) +'START_DATE = ''09/20/2010'',EXPIRY_DATE = ''01/01/2099'';'+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)PRINT @stat-----------------------------------------------------------PRINT '-- ============================================='SET @stat='--2、創建鏡像端點，同一個實例上只能存在一個鏡像端點 '+CHAR(13) +'--主機'+CHAR(13) +'CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT='+@LISTENER_PORT+' , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_' +@masteriptail +'_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );'+CHAR(13)PRINT @statSET @stat='--備機'+CHAR(13) +'CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT='+@LISTENER_PORT+' , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_' +@mirroriptail +'_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );'+CHAR(13)PRINT @statSET @stat='--見證'+CHAR(13) +'CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT='+@LISTENER_PORT+' , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_' +@witnesstail +'_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );'+CHAR(13)+CHAR(13)+CHAR(13)PRINT @stat----------------------------------------------------------------------------------------PRINT '-- ============================================='SET @stat='--3、備份證書，然后互換 '+CHAR(13) +'--主機'+CHAR(13) +'BACKUP CERTIFICATE HOST_' +@masteriptail +'_cert TO FILE = '+''''+@certpath+'\HOST_'+@masteriptail+'_cert.cer'';'+CHAR(13)PRINT @statSET @stat='--備機'+CHAR(13) +'BACKUP CERTIFICATE HOST_' +@mirroriptail +'_cert TO FILE = '+''''+@certpath+'\HOST_'+@mirroriptail+'_cert.cer'';'+CHAR(13)PRINT @statSET @stat='--見證'+CHAR(13) +'BACKUP CERTIFICATE HOST_' +@witnesstail +'_cert TO FILE = '+''''+@certpath+'\HOST_'+@witnesstail+'_cert.cer'';'+CHAR(13)+CHAR(13)+CHAR(13)PRINT @stat----------------------------------------------------------------------------------PRINT '-- ============================================='SET @stat='--4、新增主備登陸用戶 '+CHAR(13) +'--主機'+CHAR(13) +'CREATE LOGIN ['+@SLAVEHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@SLAVEHOST_NAME+'User] FOR LOGIN ['+@SLAVEHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@mirroriptail +'_cert AUTHORIZATION ['+@SLAVEHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@mirroriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@SLAVEHOST_NAME+'LoginUser];'+CHAR(13)PRINT @statSET @stat='CREATE LOGIN ['+@WITNESSHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@WITNESSHOST_NAME+'User] FOR LOGIN ['+@WITNESSHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@witnesstail+ +'_cert AUTHORIZATION ['+@WITNESSHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@witnesstail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@WITNESSHOST_NAME+'LoginUser];'+CHAR(13)PRINT @statSET @stat='--備機'+CHAR(13) +'CREATE LOGIN ['+@MASTERHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@MASTERHOST_NAME+'User] FOR LOGIN ['+@MASTERHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@masteriptail +'_cert AUTHORIZATION ['+@MASTERHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@masteriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@MASTERHOST_NAME+'LoginUser];'+CHAR(13)PRINT @statSET @stat='CREATE LOGIN ['+@WITNESSHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@WITNESSHOST_NAME+'User] FOR LOGIN ['+@WITNESSHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@witnesstail+ +'_cert AUTHORIZATION ['+@WITNESSHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@witnesstail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@WITNESSHOST_NAME+'LoginUser];'+CHAR(13)PRINT @statSET @stat='--見證'+CHAR(13) +'CREATE LOGIN ['+@MASTERHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@MASTERHOST_NAME+'User] FOR LOGIN ['+@MASTERHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@masteriptail +'_cert AUTHORIZATION ['+@MASTERHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@masteriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@MASTERHOST_NAME+'LoginUser];'+CHAR(13)PRINT @statSET @stat='CREATE LOGIN ['+@SLAVEHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@SLAVEHOST_NAME+'User] FOR LOGIN ['+@SLAVEHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@mirroriptail +'_cert AUTHORIZATION ['+@SLAVEHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@mirroriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@SLAVEHOST_NAME+'LoginUser];'+CHAR(13)PRINT @stat------------------------------------------------------------------------------PRINT '-- ============================================='SET @stat='--5、各個機器都開放5022端口，并且用telnet測試5022端口是否開通 將下面三個腳本各自粘貼到bat文件里'+CHAR(13) PRINT @statSET @stat='echo 主庫'+CHAR(13) +'telnet '+@mirrorip+' 5022'+CHAR(13) +'telnet '+@witness+' 5022'+CHAR(13) +'pause'PRINT @stat+CHAR(13)+CHAR(13)SET @stat='echo 鏡像庫'+CHAR(13) +'telnet '+@masterip+' 5022'+CHAR(13) +'telnet '+@witness+' 5022'+CHAR(13) +'pause'PRINT @stat+CHAR(13)+CHAR(13)SET @stat='echo 見證'+CHAR(13) +'telnet '+@masterip+' 5022'+CHAR(13) +'telnet '+@mirrorip+' 5022'+CHAR(13) +'pause'PRINT @stat+CHAR(13)+CHAR(13)+CHAR(13)--------------------------------------------------------------PRINT '-- ============================================='SET @stat='--6、備份數據庫(完整備份+事務日志備份)在主機執行'+CHAR(13) PRINT @statSET @stat='DECLARE @FileName NVARCHAR(MAX)'+CHAR(13)+CHAR(13)PRINT @statSET @stat='--('+@DBName+'數據庫完整備份)在主機執行'+CHAR(13) +'SET @FileName = ''D:\DBBackup\'+@DBName+'_FullBackup_1.bak'' BACKUP DATABASE ['+@DBName+'] TO DISK=@FileName WITH FORMAT ,COMPRESSION'+CHAR(13)+CHAR(13)PRINT @statSET @stat='--('+@DBName+'數據庫日志備份)在主機執行'+CHAR(13) +'SET @FileName = ''D:\DBBackup\'+@DBName+'_logBackup_2.bak'' BACKUP LOG ['+@DBName+'] TO DISK=@FileName WITH FORMAT ,COMPRESSION'PRINT @stat+CHAR(13)+CHAR(13)+CHAR(13)------------------------------------------------------------------------------PRINT '-- ============================================='SET @stat='--7、還原數據庫(指定norecovery方式還原)在備機執行'+CHAR(13) PRINT @statSET @Restorepath1=''SET @Restorepath2=@Restorepath+@DBName+'_FullBackup_1.bak' SET @SQL = 'RESTORE FILELISTONLY FROM DISK = '''+@Restorepath2+'''' INSERT INTO #BackupFileList EXEC (@SQL);DECLARE @LNAME NVARCHAR(2000)DECLARE @PNAME NVARCHAR(2000)DECLARE CurTBName CURSORFORSELECT LogicalName,PhysicalNameFROM #BackupFileList OPEN CurTBNameFETCH NEXT FROM CurTBName INTO @LNAME,@PNAMEWHILE @@FETCH_STATUS = 0BEGIN SET @Restorepath1=' MOVE N'''+@LNAME+''' TO N'''+@PNAME+''', '+CHAR(13)+@Restorepath1FETCH NEXT FROM CurTBName INTO @LNAME,@PNAMEENDCLOSE CurTBNameDEALLOCATE CurTBNameSET @stat='USE [master] RESTORE DATABASE '+@DBName+' FROM DISK = N'''+@Restorepath+@DBName+'_FullBackup_1.bak'' WITH FILE = 1,'+CHAR(13) +@Restorepath1 +'NOUNLOAD,NORECOVERY, REPLACE, STATS = 5 GO'SET @stat='USE [master] RESTORE LOG '+@DBName+' FROM DISK = N'''+@Restorepath+@DBName+'_logBackup_2.bak'' WITH FILE = 1,'+CHAR(13) +'NOUNLOAD,NORECOVERY, REPLACE, STATS = 5 GO'PRINT @stat+CHAR(13)+CHAR(13)DROP TABLE #BackupFileList--------------------------------------------------------------------------------PRINT '-- ============================================='SET @stat='--8、增加鏡像伙伴，需要先在備機上執行，再執行主機，鏡像弄好之后，默認為事務安全等級為FULL'+CHAR(13) PRINT @statSET @stat='--備機上執行'+CHAR(13) +'USE [master] GOALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@masterip+':5022''; --主機服務器的ip'+CHAR(13)+CHAR(13)PRINT @statSET @stat='--主機上執行'+CHAR(13) +'USE [master] GOALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@mirrorip+':5022''; --鏡像服務器的ip'+CHAR(13)+CHAR(13)PRINT @statSET @stat='ALTER DATABASE ['+@DBName+'] SET WITNESS = '''+'TCP://'+@witness+':5022''; --見證服務器的ip'+CHAR(13)+CHAR(13)PRINT @stat

希望對大家有幫助

?

?

最后附上鏡像相關腳本

--================================= --拆除鏡像SELECT DB_NAME([database_id]) as 'dbname',* FROM sys.[database_mirroring] GOALTER DATABASE [test] SET PARTNER OFF ALTER DATABASE [test] SET WITNESS OFF--================================= --恢復鏡像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER RESUME GO--================================= --掛起鏡像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SUSPEND GO--=================================================== --未發送的日志和未重做的日志情況 WITH tmp AS( SELECT DB_NAME(Database_id) AS DatabaseName, ROW_NUMBER()OVER(PARTITION BY Database_id ORDER BY local_time DESC) AS RID, * FROM msdb.dbo.dbm_monitor_data ) SELECT * FROM tmp WHERE RID=1--看一下redo_queue 和send_queue--================================= --刪除鏡像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER OFF GO--================================= --移除見證服務器 USE [master] GO ALTER DATABASE [Demo1] SET WITNESS OFF GO--================================= --修改為高性能模式 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SAFETY OFF GO--================================= --修改為高安全模式 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SAFETY FULL GO--================================= --在高安全下手動轉移鏡像（在主服務器上） USE [master] GO ALTER DATABASE [Demo1] SET PARTNER FAILOVER GO--================================= --在高性能下手動轉移鏡像（在從服務器上）,此時主服務器已停止 --同樣適用高安全 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS GO--================================= --在鏡像被掛起后恢復鏡像回話 --如鏡像服務器停止后又重啟時，主體服務器會被掛起，使用以下SQL來恢復鏡像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER RESUME GO--================================= --將數據庫從還原狀態轉化成正常模式 USE [master] GO RESTORE DATABASE [Demo1] WITH RECOVERY GO--================================= --修改為高安全模式 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SAFETY FULL GO--================================= --在高性能下手動轉移鏡像（在從服務器上）,此時主服務器已停止 --同樣適用高安全 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS GO

?

升級之后

USE [master] GOALTER DATABASE [testmirror] SET COMPATIBILITY_LEVEL = 120 GO/****** Object: Endpoint [Endpoint_Mirroring] Script Date: 2016/12/29 9:23:18 ******/ DROP ENDPOINT [Endpoint_Mirroring] GO

?

?

相關視圖

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-mirroring-transact-sql

?

?

如有不對的地方，歡迎大家拍磚o(∩_∩)o?

本文版權歸作者所有，未經作者同意不得轉載。

總結

以上是生活随笔為你收集整理的SQL Server镜像自动生成脚本的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。