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镜像自动生成脚本的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。