SQL Server : Database Mirroring

      
 Step 1: Take the Full Backup of Principal database using below backup command.
                
                    BACKUP DATABASE Temp TO DISK = 'C:\Program Files\Microsoft SQL 
                    Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.bak';      
      
 Step 2: Take the Transaction Log Backup of Principal database using below backup command
                 
                  BACKUP LOG Temp TO DISK = 'C:\Program Files\Microsoft SQL 
                        Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.trn';      
      
 Step 3: Copy both backup to Mirror server and  Restore the Full backup database with 
                   RESTORE WITH NORECOVERY MODE in Mirror server using below command

                  RESTORE DATABASE Temp FROM DISK = 'C:\Program Files\Microsoft SQL 
                        Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.bak' with NORECOVERY       
  
      
  Step 4: Restore Transaction Log back up in Mirror server using below command.

                  RESTORE LOG Temp FROM DISK = 'C:\Program Files\Microsoft SQL 
                        Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.trn' with NORECOVERY      
      
 Step 5: Right Click on Principal database (Temp) and choose Properties and then choose Mirror 
                   option in properties list      
      
 Step 6 : Click the "Configure Security" button and then click NEXT if the Configure Database 
                      Mirroring Security Wizard screen appears. 
                 After click on NEXT button,it will ask include Witness server or not, choose YES or NO
                      based on your requirements.      
 
      
 Step 7: Click on NEXT button, It will ask Principal Server instance details along with port number.      
      
 Step 8: Click on NEXT button, It will ask Mirror Server instance details along with port number.      
      
 Step 9: Click on NEXT button, It will ask Service account details.      
      
 Step 10: Click on NEXT button, It will configure Endpoints and Click on OK , Database Properties
                     window will appear , Click on START MIRRORING button.      
        

Some of useful Queries in Morring:

  
 To find Backup History of Database : 
      SELECT S.DATABASE_NAME,M.PHYSICAL_DEVICE_NAME,S.BACKUP_START_DATE
        ,CASE S.[TYPE] WHEN 'D' THEN 'FULL'
		     WHEN 'I' THEN 'DIFFERENTIAL'
                     WHEN 'L' THEN 'TRANSACTION LOG'END AS BACKUPTYPE
        ,S.SERVER_NAME,S.RECOVERY_MODEL
      FROM MSDB.DBO.BACKUPSET S INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY M 
        ON S.MEDIA_SET_ID = M.MEDIA_SET_ID
      WHERE S.DATABASE_NAME = DB_NAME()
      ORDER BY BACKUP_START_DATE DESC
  

  
 To Check the mirroring safety level : 
       SELECT	D.NAME, D.DATABASE_ID, M.MIRRORING_ROLE_DESC,
		M.MIRRORING_STATE_DESC, M.MIRRORING_SAFETY_LEVEL_DESC, 
		M.MIRRORING_PARTNER_NAME, M.MIRRORING_PARTNER_INSTANCE,
		M.MIRRORING_WITNESS_NAME, M.MIRRORING_WITNESS_STATE_DESC
       FROM	SYS.DATABASE_MIRRORING M JOIN SYS.DATABASES D
		ON M.DATABASE_ID = D.DATABASE_ID
       WHERE	MIRRORING_STATE_DESC IS NOT NULL
  
  
To find out about the Mirroring endpoints information
       SELECT E.NAME, E.PROTOCOL_DESC, E.TYPE_DESC, E.ROLE_DESC, E.STATE_DESC, 
		       T.PORT, E.IS_ENCRYPTION_ENABLED, E.ENCRYPTION_ALGORITHM_DESC, 
		       E.CONNECTION_AUTH_DESC 
       FROM   SYS.DATABASE_MIRRORING_ENDPOINTS E JOIN SYS.TCP_ENDPOINTS T
       ON     E.ENDPOINT_ID = T.ENDPOINT_ID 
  
  
 Setup Partner Manually,
       ALTER DATABASE Test SET PARTNER = 'TCP://(Mirror Server Name or IP) :5022' (on Principal side)
       ALTER DATABASE Test SET PARTNER = 'TCP://(Principal Server Name or IP) :5022' (on Mirror side)