How to remove custom database from BizTalk Log Shipping

I am assuming that you have already configured the BizTalk source SQL Server to take backups of your custom database by BackUpBizTalk Server job. The steps are same as mentioned in https://msdn.microsoft.com/en-us/library/aa561198.aspx.
I am also assuming that BizTalk Log Shipping is configured on the Disaster SQL Server and is working fine.

BizTalk Log Shipping is already explained in our previous blog at https://blogs.msdn.com/b/biztalknotes/archive/2012/01/10/biztalk-log-shipping.aspx?wa=wsignin1.0 .

Following shows the list of DBs which are present on the Source SQL Server along with the output of adm_OtherBackUpDatabases which shows an entry for the custom database.

 

With this setting, I enabled BackUpBizTalk Server job on the Source SQL Server.
Now I can check that BackUp job is taking FULL and TRANSACTIONAL log back up of all the BizTalk DB along with the custom DB.

 

We can see the same coming through on the Disaster SQL Server as well:

 

In above, we can also see all the DBs are in restoring state, including the custom one. So as of now BizTalk Log Shipping is working as expected.
Now suppose you want to remove the CustomDB from being backed up by BackUpBizTalk Server job, without resetting the BizTalk Log Shipping.
That can be done byfollowing the below steps:

1- Go to Source SQL Server

2- Make sure that BackUpBizTalk Server is not running at the moment.

3- Disable BackUpBizTalk Server job.

4- Remove the entry of custom DB from BizTalkMgmtDb.adm_OtherBackUpDatabase table

 

5- Go to Disaster SQL Server and make sure that all the transactional log backup belongs to (LastBackUpSetId-1) are restored. Make a note of this BackUpSetId, it is 678 in our case.

 

6- Now, enable BackUpBizTalk Server job on the Source SQL Server.

7- Wait until the next cycle of Transactional Log completes. Which is 15 minutes by default but you can change it.

8- Once the next cycle complete, go to Disaster SQL Server and check the “Restored” status of BackUpSetId which you noted in step#5, which was 678.

 

 

9- As you noticed by now, BackUpSetId 678 is also restored and as we have removed the “CustomDB” from being backed up, it is not part of the next BackUpSetId which is 679.

10- You should confirm that CustomDB is not getting backed up any more by looking in to following tables:

Source SQL Server - [BizTalkMgmtDb].[dbo].[adm_BackupHistory]
Disaster SQL Server - [master].[dbo].[bts_LogShippingHistory]

11- This(#11) step is not necessary, but if you do not want to see any failure in “BTS Log Shipping - Restore Databases” history you can do this. So once you see that another set (679 in this case) of BackUpSet
         is available on the Disaster SQL Server, go back to the Source SQL Server and disable BackUpBizTalk Server job.

12- On Disaster SQL Server, make sure that following Log Shipping jobs are not running at the moment:

  1. BTS Log Shipping - Get Backup History
  2. BTS Log Shipping - Restore Databases

13- Disable both of the above mentioned Log Shipping jobs on the Disaster SQL Server.

14- On Disaster SQL Server, open table [master].[dbo].[bts_LogShippingDatabases] for editing.

15- Delete the row corresponding to the CustomDB

 

 

16- On Source SQL Server enable BackUpBizTalk Server job.

17- One Disaster SQL Server enable following jobs:

  1. BTS Log Shipping - Get Backup History
  2. BTS Log Shipping - Restore Databases

18- Monitor the Log Shipping jobs and their history. If all the steps mentioned above are followed BizTalk Log Shipping should work as it was earlier.

19- You will still see the CustomDB in restoring state.

 

 
Things to consider while restoring the DB on Disaster SQL Server

Restoring the DB on the Disaster SQL Server is exactly same as you do it for the BizTalk Log Shipping.
However, after you restore the CustomDB will still be in the restoring state as we have deleted the entry of CustomDB from [master].[dbo].[bts_LogShippingDatabases] and Log Shipping has no idea if there is any such DB.
This is how it looks post we restore the DB on Disaster SQL Server:

To bring the CustomDB in online state, you can follow these steps:

1- Right click on CustomDB, navigate to Tasks-> Restore ->Transaction Log

 

2- On “Restore Transaction Log” screen, under “General” select “From file or tape”

3- Click on the ellipsis button adjacent to “From file or tape”

4- On the “Specify BackUp” page, select “BackUp media “as File, click on “Add” and navigate to the folder where you have the backups of CustomDB and then select the last transactional log backup available for the CustomDB. Click Ok.

  

5- Up on returning back to “Restore Transaction Log” page, select “Options” under “Select a page”

6- Then select “RESTORE WITH RECOVERY” from “Recovery State”.

7- Click on Ok.

8- Your CustomDB should be restored successfully.

Note: It is not recommended to play around with the BizTalk logshipping databases in a production environment. Please test the above steps in a test environment before applying on the production environment.

 Written by
Jainath V R and Chirag Pavecha

Reviewed by
Chirag Pavecha

Microsoft GTSC