SQL database backup and Recovery mirroring configuration (certificate mode)

Recently, the company is working on data storage disaster and backup, recording a summary of the image configuration

Step one: Back up and restore the database--back up the primary database with full and transaction logs--restore full and transaction log backups from the server, and select Do not perform any operations on the database (with NORECOVERY)Step Two: master Server A configuration certificate--Create master KeyCREATEMasterKEYEncryption byPassword= 'Passworda' ;GO--Create a certificateCREATECERTIFICATE cert_a withSubject= 'a certificate' ;GO--Creating EndpointsIF EXISTS(SELECT 1  fromSys. EndpointsWHEREName= 'Primary Image' )  DROPEndpoint[Primary Image]GOCREATEEndpoint[Primary Image]  AUTHORIZATION [SA] State=started asTCP (Listener_port= 5022, LISTENER_IP=  All)   forData_mirroring (ROLE=Partner, authentication=CERTIFICATE Cert_a, encryption=required algorithm RC4)GO--Export CertificateBACKUPCERTIFICATE cert_a to FILE = 'H:\MyCert\Cert_A.cer'Step Three: Configure certificates from server B--Create master KeyCREATEMasterKEYEncryption byPassword= 'Passwordb' ;GO--Create a certificateCREATECERTIFICATE Cert_b withSubject= 'B Certificate' ;GO--Creating EndpointsIF EXISTS(SELECT 1  fromSys. EndpointsWHEREName= 'from the Mirror' )  DROPEndpoint[from the Mirror]GOCREATEEndpoint[from the Mirror]  AUTHORIZATION [SA] State=started asTCP (Listener_port= 5022, LISTENER_IP=  All)   forData_mirroring (ROLE=Partner, authentication=CERTIFICATE Cert_b, encryption=required algorithm RC4)GO--Export CertificateBACKUPCERTIFICATE cert_cy08 to FILE = 'C:\Cert_B.cer'Step four: Exchange Certificate Step five: master Server A creates a login user UseMaster;--Create a login nameCREATELOGIN B_login withPASSWORD= 'Passworda';GO--Create userCREATE USERCy08_user forLOGIN B_login;GO--Restore B CertificateCREATECERTIFICATE Cert_bAUTHORIZATIONB_user from FILE = 'H:\MyCert\Cert_B.cer'GO--Authorization endpoint to login nameGRANTCONNECT onENDPOINT::[Primary Image]  to [B_login]; step Six: Create a login user from Server a UseMaster;--Create a login nameCREATELOGIN A_login withPASSWORD= 'Passworda';GO--Create userCREATE USERA_user forLOGIN A_login;GO--Restore B CertificateCREATECERTIFICATE cert_aAUTHORIZATIONA_user from FILE = 'C:\Cert_A.cer'GO--Authorization endpoint to login nameGRANTCONNECT onENDPOINT::[from the Mirror]  to [A_login]; Step seven: Start Mirroring--execute from database, connect mirror UseMasterGO  ALTER DATABASECYEMSSETPARTNER= 'tcp://192.168.0.a:5022'; GO--Primary Database Execution UseMasterGOALTER DATABASECYEMSSETPARTNER= 'tcp://192.168.0.b:5022';GORelated Scripts--stop mirroring, then reconnect; issues that occur during configuration are availableALTER DATABASECYEMSSETPARTNEROFF;--High security mode, off for asynchronous modeALTER DATABASECYEMSSETSAFETY Full;--Switch Master and standby, can be used for database upgrade maintenanceALTER DATABASE [DBName] SETPARTNER FAILOVER;--force the Mirror Library to be set as the primary library, allowing data loss when the main library fails to connectAlterdatabase[DBName]Setpartnerforce_service_allow_data_loss;--Restore ImageALTER DATABASE [DBName] SETPARTNER RESUME;--set up Database onlineALTER DATABASE [DBName] SETONLINE;--in master standby: Create a Database Mirroring Monitor job Usemsdb; execsys. sp_dbmmonitoraddmonitoring--exec sys.sp_dbmmonitorhelpmonitoring--exec sys.sp_dbmmonitorresults dbname,0,0--exec sys.sp_dbmmonitorchangemonitoring--exec sys.sp_dbmmonitordropmonitoring

This method of image backup is used for multiple servers running simultaneously, in the database restore and backup to achieve a short time in a timely backup, in the database files the next day backup or monthly weekly backup on the dimension is expanded, small and medium-sized databases have good use.

 

SQL database backup and Recovery mirroring configuration (certificate mode)