SQLMirroring[HotbackupwithDoublemachine](二)

Script in Witness:

-- Step 1
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Password01!';
CREATE CERTIFICATE HOST_pro_cert WITH SUBJECT = 'HOST_Witness certificate' , 
START_DATE = '01/11/2014', EXPIRY_DATE = '01/11/2015';

-- Step 2
alter ENDPOINT Endpoint_Mirroring 
	STATE = STARTED 
	AS TCP ( LISTENER_PORT=50221 , LISTENER_IP = ALL ) 
	FOR DATABASE_MIRRORING 
    ( AUTHENTICATION = CERTIFICATE HOST_pro_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )
	select * from sys.endpoints
Go

-- Step 3
BACKUP CERTIFICATE HOST_pro_cert TO FILE = 'D:\Hot backup\HOST_pro_cert.cer';  --Note: Full Name

-- Step 4
CREATE LOGIN HOST_min_login WITH PASSWORD ='Password01!';
CREATE USER HOST_min_user FOR LOGIN HOST_min_login;
CREATE CERTIFICATE HOST_min_cert AUTHORIZATION HOST_min_user FROM FILE ='D:\Hot backup\HOST_min_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_min_login];   

CREATE LOGIN HOST_pri_login WITH PASSWORD ='Password01!';
CREATE USER HOST_pri_user FOR LOGIN HOST_pri_login;
CREATE CERTIFICATE HOST_pri_cert AUTHORIZATION HOST_pri_user FROM FILE ='D:\Hot backup\HOST_pri_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pri_login];
--select * from sys.database_principals
Go

-- Step 5
Create database TestBackup01

USE master;
ALTER DATABASE TestBackup01
SET RECOVERY FULL;
RESTORE DATABASE TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;
RESTORE LOG TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH FILE = 2, NORECOVERY, NOUNLOAD
Go

Note:

1. copy the certificates over to the other server(Mirror, Witness and Principal).

2. After coping the certificates over I setup the logins.

3. Back up and restore db with log.

• Perform a Transaction Log backup on the Principal server.
• Copy the ba