SQL Server镜像自动生成脚本

时间：2022-12-27 23:42:12

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

END

CREATE 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 ON

SET @masterip='172.16.198.254'  --★Do

SET @mirrorip='172.16.198.253'   --★Do

SET @witness=''   --★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=''  --★Do

SET @MASTERHOST_NAME='A'  --★Do

SET @SLAVEHOST_NAME='B'  --★Do

SET @WITNESSHOST_NAME='C'  --★Do

select @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 @stat

SET  @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 @stat

SET  @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 @stat

SET  @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 @stat

SET  @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 @stat

SET  @stat='--备机'+CHAR(13)

+'BACKUP CERTIFICATE HOST_'

+@mirroriptail

+'_cert TO FILE = '+''''+@certpath+'\HOST_'+@mirroriptail+'_cert.cer'';'+CHAR(13)

PRINT @stat

SET  @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 @stat

SET  @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 @stat

SET  @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 @stat

SET  @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 @stat

SET  @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 @stat

SET  @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 @stat

SET  @stat='echo 主库'+CHAR(13)

+'telnet '+@mirrorip+''+CHAR(13)

+'telnet '+@witness+''+CHAR(13)

+'pause'

PRINT @stat+CHAR(13)+CHAR(13)

SET  @stat='echo 镜像库'+CHAR(13)

+'telnet '+@masterip+''+CHAR(13)

+'telnet '+@witness+''+CHAR(13)

+'pause'

PRINT @stat+CHAR(13)+CHAR(13)

SET  @stat='echo 见证'+CHAR(13)

+'telnet '+@masterip+''+CHAR(13)

+'telnet '+@mirrorip+''+CHAR(13)

+'pause'

PRINT @stat+CHAR(13)+CHAR(13)+CHAR(13)

--------------------------------------------------------------

PRINT '-- ============================================='

SET  @stat='--6、备份数据库(完整备份+事务日志备份)在主机执行'+CHAR(13)

PRINT @stat

SET  @stat='DECLARE @FileName NVARCHAR(MAX)'+CHAR(13)+CHAR(13)

PRINT @stat

SET  @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 @stat

SET  @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 @stat

SET  @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 CURSOR

        FOR

            SELECT LogicalName,PhysicalName

            FROM    #BackupFileList  

        OPEN CurTBName

        FETCH NEXT FROM CurTBName INTO @LNAME,@PNAME

        WHILE @@FETCH_STATUS = 0

            BEGIN

             SET  @Restorepath1=' MOVE N'''+@LNAME+''' TO N'''+@PNAME+''', '+CHAR(13)+@Restorepath1

                FETCH NEXT FROM CurTBName INTO  @LNAME,@PNAME

            END

        CLOSE CurTBName

        DEALLOCATE CurTBName

SET  @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 @stat

SET  @stat='--备机上执行'+CHAR(13)

+'USE [master]

GO

ALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@masterip+':5022'';  --主机服务器的ip'+CHAR(13)+CHAR(13)

PRINT @stat

SET  @stat='--主机上执行'+CHAR(13)

+'USE [master]

GO

ALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@mirrorip+':5022'';  --镜像服务器的ip'+CHAR(13)+CHAR(13)

PRINT @stat

SET  @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]

GO

ALTER 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]

GO

ALTER 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 

本文版权归作者所有，未经作者同意不得转载。

相关文章