Configuring SQL Server Log Shipping using SSMS

Stacy Brown | January 11th, 2019 | SQL Transaction Log |

As we have discussed in a previous article Transaction log shipping is a simple SQL Server high availability technology that offers disaster recovery protection at the database level. Now, in this article we are going to discuss how to configure SQL Server log shipping using SQL Server Management Studio. Transaction log shipping contains the primary server and one or more than one secondary server.

Prerequirements to Configure SQL Server Log Shipping

Before you start to configure SQL Server log shipping

  • The primary database needs full recovery or bulk-logged recovery module
  • All the server should have a same case sensitivity setting which involved in transaction log shipping
  • SQL Server 2005 or later edition should be installed
  • You must have a sysadmin membership to configure SQL Server log shipping

As I said earlier, the database must use full or bulk-logged recovery module if it is not in full or bulk-logged recovery module then you can change it by using the following query:

Change recovery model

Configure SQL Server Log Shipping Environment

  1. Connect to the primary server
  2. sql login

  3. Right click on the database in SSMS (SQL Server Management Studio) which you want to use as a primary database and select properties
  4. Database proper

  5. Select the Transaction Log Shipping page under Select a Page column
  6. Click on Enable this as a primary database in a log shipping configuration box
  7. Click on Backup Setting under Transaction Log Backup to schedule transaction log backup
  8. database properties

  9. Define the backup setting (i.e. path to backup folder, delete file older then, alert if no backup occurs within, backup job, backup compression)
  10. Transaction log backup setting

  11. Click on Add button under Secondary server instance and database
  12. secondary database

  13. Click Connect button to connect to the secondary server instance
  14. transaction SQL Server log shipping

  15. Choose a database or type a new database name in Secondary database box
  16. configure SQL Server log shipping

  17. In the next step secondary server gives you the access on three tables, i.e.
    • Initialize Secondary Database tab
    • Copy File tab
    • Restore Transaction Log tab
  18. In Initialize Secondary Database tab you have three options to specify the data structure on secondary server as shown below
    • Generate full backup of the primary database and restore it into the secondary database
    • Restore an existing backup of the primary database into the secondary database
    • The secondary database is initialized

    Initialize Secondary Database

  19. In Copy file tab you have to specify the path of the folder where the transaction log backup should be copied
  20. copy files

  21. In Restore Transaction log tab you have to specify the database state when restoring database
  22. configure SQL Server log shipping

  23. Check Use a monitor server instance, under Monitor server instance, which will notify us in case of any failure and then click on Setting button
  24. monitor server instance

  25. In Log Shipping Monitor Setting window, click on Connect button
  26. Select the connection method under Monitor connection to be used by a backup, copy and restore jobs connect to this server instance
  27. log shipping monitor setting

  28. To finish the transaction log shipping configuration, click on the ok button

Conclusion

Transaction log shipping is a simple and supreme solution that provides disaster recovery protection. This topic described step by step process to configure SQL Server log shipping and some important things you had to keep in your mind before you start to configure SQL Server log shipping.