Setup SQL Database Mirroring using T-SQL between SQL2005 to SQL2012

	SQL 2005SP4	SQL2012SP1
	USE [master] GO CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'donghua', @rolename = N'sysadmin' GO	USE [master] GO CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [donghua] GO
	create database TestDb; go use TestDb; go create table t(c1 varchar(20)); insert into t values(getdate()); go select * from t;
Backup & Restoration
Set Full recovery model	USE master; GO ALTER DATABASE TestDB SET RECOVERY FULL; GO
Backup Database	BACKUP DATABASE TestDb     TO DISK = 'C:\Backup\TestDb_Full.bak'     WITH FORMAT GO	Copy the backup to this Server
Restore Database		RESTORE DATABASE TestDb    FROM DISK='C:\backup\TestDb_Full.bak'    WITH NORECOVERY,       MOVE 'TestDb' TO          'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.mdf',       MOVE 'TestDb_log' TO          'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb_log.LDF'; GO
Backup Log	BACKUP LOG TestDb     TO DISK = 'C:\Backup\TestDb_Log.bak'     WITH FORMAT GO	Copy the backup to this Server
		RESTORE LOG TestDb     FROM DISK = 'C:\Backup\TestDb_Log.bak'     WITH NORECOVERY GO
The two server instances run in nontrusted Windows domains, so certificate-based authentication is required
On the master database, create the database master key, if needed.	USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e'; GO	USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '093j45&*he'; GO
Make a certificate for this server instance.	USE master; GO CREATE CERTIFICATE SQL2005_cert    WITH SUBJECT = 'SQL2005 certificate',    START_DATE = '20121031',    EXPIRY_DATE = '20151031'; GO	USE master; GO CREATE CERTIFICATE SQL2012_cert    WITH SUBJECT = 'SQL2012 certificate',    START_DATE = '20121031',    EXPIRY_DATE = '20151031'; GO
Create a mirroring endpoint for server instance using the certificate.	CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (       LISTENER_PORT=7024       , LISTENER_IP = ALL    )    FOR DATABASE_MIRRORING (       AUTHENTICATION = CERTIFICATE SQL2005_cert       , ENCRYPTION = REQUIRED ALGORITHM AES       , ROLE = ALL    ); GO	CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (       LISTENER_PORT=7024       , LISTENER_IP = ALL    )    FOR DATABASE_MIRRORING (       AUTHENTICATION = CERTIFICATE SQL2012_cert       , ENCRYPTION = REQUIRED ALGORITHM AES       , ROLE = ALL    ); GO
Back up the HOST_A certificate, and copy it to other system, HOST_B.	BACKUP CERTIFICATE SQL2005_cert TO FILE = 'C:\Backup\SQL2005_cert.cer'; GO	BACKUP CERTIFICATE SQL2012_cert TO FILE = 'C:\Backup\SQL2012_cert.cer'; GO
Setup inbound connection
Create a login on HOST_A for HOST_B.	USE master; CREATE LOGIN sql2012_login WITH PASSWORD = '1Sample_Strong_Password!!#'; GO	USE master; CREATE LOGIN sql2005_login WITH PASSWORD = '2Sample_Strong_Password!!#'; GO
Create a user for that login.	CREATE USER sql2012_user FOR LOGIN sql2012_login; GO	CREATE USER sql2005_user FOR LOGIN sql2005_login; GO
Associate the certificate with the user.	CREATE CERTIFICATE SQL2012_cert    AUTHORIZATION sql2012_user    FROM FILE = 'C:\backup\SQL2012_cert.cer' GO	CREATE CERTIFICATE SQL2012_cert    AUTHORIZATION sql2012_user    FROM FILE = 'C:\backup\SQL2012_cert.cer' GO
Grant CONNECT permission on the login for the remote mirroring endpoint.	GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login]; GO	GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login]; GO
Setup mirror database
On the mirror server instance on HOST_B, set the server instance on HOST_A as the partner (making it the initial principal server instance).		--At HOST_B, set server instance on HOST_A as partner (principal server): ALTER DATABASE TestDb     SET PARTNER = 'TCP://WIN2003SQL2005:7024'; GO
On the principal server instance on HOST_A, set the server instance on HOST_B as the partner (making it the initial mirror server instance)	--At HOST_A, set server instance on HOST_B as partner (mirror server). ALTER DATABASE TestDb     SET PARTNER = 'TCP://WIN-HN1Q39OK9JF:7024'; GO
This example assumes that the session will be running in high-performance mode. To configure this session for high-performance mode, on the principal server instance (on HOST_A), set transaction safety to OFF.	--Change to high-performance mode by turning off transacton safety.                                                     ALTER DATABASE TestDb     set partner SAFETY off GO
Setup mirror database
Create database snapshot		CREATE DATABASE TestDb_SS ON ( NAME = TestDb, FILENAME = 'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.ss' ) AS SNAPSHOT OF TestDb; GO Msg 946, Level 14, State 1, Line 1 Cannot open database 'TestDb_SS' version 611. Upgrade the database to the latest version. Msg 1823, Level 16, State 7, Line 1 A database snapshot cannot be created because it failed to start.
Perform insert in SQL2005	use testdb go insert into t values(getdate()); go
Manual Failover	USE master go ALTER DATABASE TestDb SET PARTNER FAILOVER go Msg 1477, Level 16, State 1, Line 1 The database mirroring safety level must be FULL to manually failover database "TestDb".  Set safety level to FULL and retry.
Force Service in a Database Mirroring Session		use master; go ALTER DATABASE TestDb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS go Msg 1455, Level 16, State 106, Line 1 The database mirroring service cannot be forced for database "TestDb" because the database is not in the correct state to become the principal database.
Use the restore command to open the DB		Use master go ALTER DATABASE TestDb SET PARTNER OFF go RESTORE DATABASE TestDb WITH RECOVERY go -- Data is in-sync