SQL Server镜像自动生成脚本(四)
B_03_Mirror];'+CHAR(13)
PRINT @stat
SET @stat='--备机'+CHAR(13)
+'CREATE LOGIN DB_01_Mirror WITH PASSWORD = '''+@LOGINPWD+''';
CREATE USER DB_01_Mirror FOR LOGIN DB_01_Mirror;
CREATE CERTIFICATE HOST_'
+@masteriptail
+'_cert AUTHORIZATION DB_01_Mirror FROM FILE ='''+@certpath+'HOST_'+@masteriptail+'_cert.cer'';'
+'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DB_01_Mirror];'+CHAR(13)
PRINT @stat
SET @stat='CREATE LOGIN DB_03_Mirror WITH PASSWORD = '''+@LOGINPWD+''';
CREATE USER DB_03_Mirror FOR LOGIN DB_03_Mirror;
CREATE CERTIFICATE HOST_'
+@witnesstail
+'_cert AUTHORIZATION DB_03_Mirror FROM FILE ='''+@certpath+'HOST_'+@witnesstail+'_cert.cer'';'
+'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DB_03_Mirror];'+CHAR(13)
PRINT @stat
SET @stat='--见证'+CHAR(13)
+'CREATE LOGIN DB_01_Mirror WITH PASSWORD = '''+@LOGINPWD+''';
CREATE USER DB_01_Mirror FOR LOGIN DB_01_Mirror;
CREATE CERTIFICATE HOST_'
+@masteriptail
+'_cert AUTHORIZATION DB_01_Mirror FROM FILE ='''+@certpath+'HOST_'+@masteriptail+'_cert.cer'';'
+'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DB_01_Mirror];'+CHAR(13)
PRINT @stat
SET @stat='CREATE LOGIN DB_02_Mirror WITH PASSWORD = '''+@LOGINPWD+''';
CREATE USER DB_02_Mirror FOR LOGIN DB_02_Mirror;
CREATE CERTIFICATE HOST_'
+@mirroriptail
+'_cert AUTHORIZATION DB_02_Mirror FROM FILE ='''+@certpath+'HOST_'+@mirroriptail+'_cert.cer'';'
+'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DB_02_Mirror];'+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13)
PRINT @stat
------------------------------------------------------------------------------
PRINT '-- ============================================='
SET @stat='--5、各个机器都开放5022端口，并且用telnet测试5022端口是否开通 将下面三个脚本各自粘贴到bat文件里'+CHAR(13)
PRINT @stat
SET @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 @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 DATABASE ['+@DBName+']
TO DISK=@FileName WITH FORMAT ,COMPRESSION'
PRINT @stat+CHAR(13)+CHAR(13)+CHAR(13)
------------------------------------------------------------------------------
PRINT '-- ============================================='
SET @stat='--7、还原数据库(指定norecovery方式还原)'+