SQL server2016镜像备份（不需要见证服务器）实战亲测！_sql server 2016数据库镜像

--user master 
--a.创建数据库主密钥 
create master key encryption by password = 'qwe123'; 
--可用以下语句查看生成的数据库主密钥 
--select * from   sys.symmetric_keys ;

--b.创建一个证书  
create certificate db_host_a_cert with subject = 'db_host_a certificate for database mirroring', start_date = '02/12/2016', expiry_date = '12/31/2039' 
--db_host_a_cert 证书名字 
--查询证书 
--select * from sys.certificates; 

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'kk_2020'; 
############################返回提示########################### 
消息 15578，级别 16，状态 1，第 1 行 数据库中已存在主密钥。执行此语句前，请先删除该主密钥。

DROP MASTER KEY 
############################返回提示########################### 
消息 15580，级别 16，状态 1，第 1 行 无法删除 主密钥，因为 证书 'db_host_a_cert' 是由它加密的。

DROP CERTIFICATE db_host_a_cert 
############################返回提示########################### 
命令已成功完成。 
###若提示正在有一个或多个端点正在使用，要先
drop endpoint xxx端点名
 
###########################删除主密钥########################### 
DROP MASTER KEY 
############################返回提示########################### 
命令已成功完成。
 

--c.创建镜像端点  
create endpoint db_mirr state = started as tcp(listener_port=5022,  --镜像端点使用的通信端口
listener_ip = all)                             -- 侦听的IP地址    
for database_mirroring (    
authentication = certificate db_host_a_cert,   -- 证书身份验
encryption = required algorithm rc4,           -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    
role = all);                                   -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴) 
 
--查询 
--select * from sys.tcp_endpoints        
--select * from sys.database_mirroring_endpoints; 

 备份证书

--d.备份证书  
backup certificate db_host_a_cert to file = 'D:\ShareFile\db_a_run.cer';

--e.创建登入  
create login to_host_a_login with password = 'qwe123'; 
create user to_host_a_user for login to_host_a_login; 
******************************************************* 
--有问题的时候需要删除之前的用户 
--drop login to_host_a_login，drop user to_host_a_user

-- user master 
--1.创建数据库主密钥  
create master key encryption by password = 'qwe123'; 
--可用以下语句查看生成的数据库主密钥  
--select * from   sys.symmetric_keys ; 

--2.创建一个证书  
create certificate db_host_b_cert with subject = 'db_host_b certificate for database mirroring', start_date = '02/12/2016', expiry_date = '12/31/2029' 
--查询 
--select * from sys.certificates; 

--3.创建镜像端点  
create endpoint db_mirr state = started as tcp( listener_port=5022 ,listener_ip = all ) for database_mirroring( authentication = certificate db_host_b_cert, encryption = required algorithm rc4, role = all); 
--查询 
--select * from sys.tcp_endpoints  
--select * from sys.database_mirroring_endpoints; 
 

--4.备份证书  
backup certificate db_host_b_cert to file = 'D:\ShareFile\db_b_run.cer';

--5.创建登入  
create login to_host_b_login with password = 'qwe123'; 
create user to_host_b_user for login to_host_b_login;

use master create certificate db_host_b_cert authorization to_host_a_user from file = 'D:\ShareFile\db_b_run.cer'
 
--赋权 
 
grant connect on endpoint::db_mirr to [to_host_a_login];

use master create certificate db_host_a_cert authorization to_host_b_user from file = 'D:\ShareFile\db_a_run.cer'
 
--赋权 
 
grant connect on endpoint::db_mirr to [to_host_b_login];
 

alter database DB_1 set partner = 'tcp://test001.xxx.net:5022';

alter database DB_2 set partner = 'tcp://test002.xxx.net:5022';

Create  PROC [dbo].[CleanTranLog] 
AS     
    BEGIN         
        DECLARE @num TINYINT --执行次数         
        DECLARE @backLogName VARCHAR(100) ;--备份日志文件名称         
        DECLARE @backLogPath VARCHAR(100) ; --备份日志文件的路径         
        SET @num = 0 ;         
        SET @backLogPath = N'C:\SQLBackup' ;--设定需要备份日志的路径         
        --备份3次镜像日志文件，同时删除         
        WHILE( @num < 3 )             
            BEGIN                 
                DECLARE @LogPath VARCHAR(100)                 
                SET @backLogName = CAST(@num as VARCHAR(2)) + '.trn' ;                 
                SET @LogPath = @backLogPath + '\' + @backLogName                 
                BACKUP LOG DB  TO DISK = @LogPath WITH NOFORMAT, NOINIT,
                    NAME= @backLogName, SKIP, REWIND, NOUNLOAD,STATS = 10                 
                SET @num = @num + 1                 
                --删除刚备份的trn日志文件结束的备份日志文件                 
                EXECUTE master.dbo.xp_delete_file 0, @LogPath ;             
            end          
            --收缩日志文件到300M         
            DBCC SHRINKFILE (DB_log, 300) ;                       
            --注意         
            --DB 这里指： 数据库名称         
            --DB_log 为日志逻辑名称 可以通过数据库属性中“文件”查看日志的逻辑名称。     
    END

USE [testdb]     
--数据库 
GO exec CleanTranLog        
--需要执行的存储过程