Log Shipping – NORECOVERY vs. STANDBY mode

 

1. Principal Instance - Put the database you want to mirror in FULL recovery mode.
   For example:

      1:  BACKUP DATABASE AdventureWorksLT2008 

   
   
   

      2:  TO DISK = 'c:\AdventureWorksLT2008.bak'

   
   
   

      3:  

   
   
   

      4:  BACKUP LOG AdventureWorksLT2008 

   
   
   

      5:  TO DISK = 'c:\AdventureWorksLT2008.trn'

   
   



2. Copy the backups you made on the Principal Instance to the Mirror Instance



3. Mirror Instance - Restore the database and log with the NORECOVERY option
   

      1:  RESTORE Database AdventureWorksLT2008 

   
   
   

      2:  FROM DISK = 'c:\AdventureWorksLT2008.bak'

   
   
   

      3:      WITH MOVE 'AdventureWorksLT2008_Data' TO 'E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008.mdf',

   
   
   

      4:      MOVE 'AdventureWorksLT2008_Log' TO 'E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008.ldf',

   
   
   

      5:  NORECOVERY

   
   
   

      6:  

   
   
   

      7:  RESTORE LOG AdventureWorksLT2008

   
   
   

      8:      FROM DISK = 'c:\AdventureWorksLT2008.trn' 

   
   
   

      9:  WITH NORECOVERY

   
   



4. On the Principal Instance right click on the database you are going to mirror and select Tasks > Mirror...



5. Click on the Configure Security... button. This will launch the Configure Database Mirroring Security Wizard.



6. Click the Next button on the initial screen. On the Include Witness Server dialog select No



7. On the Principal Server Instance dialog leave the default and click Next



8. On the Mirror Server Instance dialog enter in the name of the server where you will be mirroring to. Click the Connect... button. Leave the port and endpoint name with the defaults and click on the Next button.



9. Enter in the names of the accounts used to mirror the data. Since we have a domain I would recommend leaving these boxes empty. Click the Finish button.



10. If everything goes well you should see a dialog box documenting the different endpoints and mirroring configuration. Click on the Do Not Start Mirroring Button. This way we can review the settings before we start mirroring.



11. Change the operating mode to High performance (asynchronous)



12. Click on the Start Mirroring Button. After a few moments you should see the status change to "Synchronized"