Test A Database Log shipping to multiple SQL Server instances

Today I tested a logshipping for multiple database instances (Test Database dblogtest, master server instance sql2k8, secondary1 instance R2, secondary2 instance sql28). The steps are as follows:

 

1. Back up the logshipping database in sql2k8

 

2. Restore the database (norecovery) on R2 and sql28)

 

3. Configure log transfer to R2 on sql2k8 (you need to enter the Backup and Restore paths), and click OK to export the script.

 

4. Copy the part of the exported script executed in secondary to sql28, and change the copy folder path (also the restore path)

 

5. Run the script. After the script is successfully executed, two jobs (copy/restore) are generated)

 

6. Manual copy/restore job execution on R2 and secondary2 is successful.

 

7. In R2 and secondary2 queries, we can see that primary_server is sql2k8

 

Use MSDB

Go

Select *
Fromlog_shipping_monitor_secondary

 

8. Change the data on the master server sql2k8 dblogtest and run the backup job.

 

9. Repeat Step 6.

 

10. Publish the R2 and secondary2 databases (restore with recovery)

11. Check that the test data has been updated.

 

Note that the backup retention time may need to be adjusted to ensure that logs are not lost on both servers.