none
Backup and restore operations

    Question

  • I understand that recommended mehtod to backup and restore BTS is by 1st, configuring the several backupand markup log jobs on the box running BTS DBs, 2nd configure Log shipping towards a 2nd sql 2005 (if source sql2008 then destination shouldbe as well 2008).
    For 2nd step, BTS ships with 2 sql files "LogShipping_Destination_Schema.sql" and "LogShipping_Destination_Logic.sql" which involves a lot of creation (Tables and SP) in master DB as well as manual configuration.
    1st Question : Is there any cleanup script in order to revert back if for whatever reason I want to stop this operation and clean my destination environment?
    2nd Question : backup directory will be receiving bak files, "Clear Backup History" step in Backup BizTalk Server (BizTalkMgmtDb) job seems clean the history table but does not delete backup files? Should we delete manually those files? if yes, this is very prone to errors as somebody might delete some files needed by a backyup set? is there any parameter or script to do it automaticlaly inside those steps?

    thanks in advance
    Sunday, January 03, 2010 8:35 PM

Answers


  • Hi eliassal,

    I've written a series of blog posts regarding BizTalk Log Shipping that might be of interest (links at the bottom of this reply). To answer your questions:

    1. Is there any cleanup script in order to revert back if for whatever reason I want to stop this operation and clean my destination environment?

    Yes, there is a cleanup script that is provided out of the box - run the master.dbo.bts_LogShippingClean stored procedure on the standby/DR environment. This stored procedure deletes all references to the previously restored backup-sets and physically deletes the databases that are in the ‘restoring’ state. These two actions force the BTS Log Shipping – Restore Databases job to re-create the databases in a restoring state by applying the last full backup, followed by subsequent transaction log backups the next time it runs.

    More information on the LogShipClean stored procedure can be found in Part 2 of my series on BizTalk DR (see below)

    2. Backup directory will be receiving bak files, "Clear Backup History" step in Backup BizTalk Server (BizTalkMgmtDb) job seems clean the history table but does not delete backup files?

    You are correct in that the backup files themselves are not deleted and have to be managed manually or via a script. For customers in the past, I usually write a small vbs script that deletes backup files older than x days which normally works well, however this script could be extended to check the LogShippingHistory table on the standby environment and only delete those backup files that have been successfully restored. With regards to security, the directory that contains the backup files can be locked down to just a few users to prevent the files being deleted.

    Both posts can be found at my blog: http://www.modhul.com/2009/06/29/configuring-biztalk-for-disaster-recovery-part-1/ & http://www.modhul.com/2009/09/04/configuring-biztalk-backup-for-disaster-recovery-part-2/

    Hope this helps, Nick.


    Nick Heppleston’s BizTalk Blog - Experiences of a UK BizTalk Consultant (http://www.modhul.com)
    Looking for a fast and efficient way to archive messages in BizTalk? Try Atomic-Scope's new BizTalk Message Archiving Pipeline Component - Download a free 14-day trial!
    Thursday, January 07, 2010 12:07 PM

All replies

  • 1st Question : Is there any cleanup script in order to revert back if for whatever reason I want to stop this operation and clean my destination environment?
    I am sorry, but I do not know the answer to this.
    2nd Question : backup directory will be receiving bak files, "Clear Backup History" step in Backup BizTalk Server (BizTalkMgmtDb) job seems clean the history table but does not delete backup files? Should we delete manually those files? if yes, this is very prone to errors as somebody might delete some files needed by a backyup set? is there any parameter or script to do it automaticlaly inside those steps?

    There is no way of getting BizTalk to do it - you will need to either delete them manually or setup some job to do it for you.
    eliasen, representing himself and not the company he works for.
    Three times MVP and three times MCTS in BizTalk.
    Blog: http://blog.eliasen.dk
    Monday, January 04, 2010 8:09 AM

  • Hi eliassal,

    I've written a series of blog posts regarding BizTalk Log Shipping that might be of interest (links at the bottom of this reply). To answer your questions:

    1. Is there any cleanup script in order to revert back if for whatever reason I want to stop this operation and clean my destination environment?

    Yes, there is a cleanup script that is provided out of the box - run the master.dbo.bts_LogShippingClean stored procedure on the standby/DR environment. This stored procedure deletes all references to the previously restored backup-sets and physically deletes the databases that are in the ‘restoring’ state. These two actions force the BTS Log Shipping – Restore Databases job to re-create the databases in a restoring state by applying the last full backup, followed by subsequent transaction log backups the next time it runs.

    More information on the LogShipClean stored procedure can be found in Part 2 of my series on BizTalk DR (see below)

    2. Backup directory will be receiving bak files, "Clear Backup History" step in Backup BizTalk Server (BizTalkMgmtDb) job seems clean the history table but does not delete backup files?

    You are correct in that the backup files themselves are not deleted and have to be managed manually or via a script. For customers in the past, I usually write a small vbs script that deletes backup files older than x days which normally works well, however this script could be extended to check the LogShippingHistory table on the standby environment and only delete those backup files that have been successfully restored. With regards to security, the directory that contains the backup files can be locked down to just a few users to prevent the files being deleted.

    Both posts can be found at my blog: http://www.modhul.com/2009/06/29/configuring-biztalk-for-disaster-recovery-part-1/ & http://www.modhul.com/2009/09/04/configuring-biztalk-backup-for-disaster-recovery-part-2/

    Hope this helps, Nick.


    Nick Heppleston’s BizTalk Blog - Experiences of a UK BizTalk Consultant (http://www.modhul.com)
    Looking for a fast and efficient way to archive messages in BizTalk? Try Atomic-Scope's new BizTalk Message Archiving Pipeline Component - Download a free 14-day trial!
    Thursday, January 07, 2010 12:07 PM
  • Nick, really appreciate your response and both blog entries. Before reading them, I knew and implemented several SPs but really your details gave me full idea and a sound understanding of the process.

    Can you please point me to the third part as I am interested of playing the whole scenario in my environment.

    Thanks again for making this information available for the coumunity. It seems to me that you are involved in Biztalk admin stuff, I would like to invite you to visit my blog at http://salam.hd.free.fr/BlogEngine/ where I made available a demonstration of a nice handy tool I called Biztalkdashboard. I would be more than happy to receive your comments.

    Best regards,
     

    Sunday, January 10, 2010 11:51 AM
  • Eliassal,

    Part three of the BizTalk Log Shipping series is still in the pipeline I'm afraid - I should have it out within the next few weeks. I'd be happy to take a look at your BizTalk Dashboard solution - I'll contact you directly (rather than through this forum) with any comments.

    Kind regards, Nick.

    Nick Heppleston - Independent UK BizTalk Consultant (http://www.modhul.com)

    Please mark as answered if this answers your question.

    Looking for a fast and efficient way to archive messages in BizTalk? Try Atomic-Scope's new BizTalk Message Archiving Pipeline Component. Download a free 14-day trial!

  • Nick, I have setupan environment and managed to do everything, thanks to your documentation. However, while I was implementing the last step in "How to Configure the Destination System for Log Shipping>> update SampleUpdateInfo.xml ", I noticed within BAM tags the following tags ;

    <DeploymentUnit Name="AnalysisDatabase">
       <Property Name="ServerName">DestinationServer</Property>
       <Property Name="DatabaseName">BAMAnalysis</Property>
      </DeploymentUnit>
    <DeploymentUnit Name="StarSchemaDatabase">
       <Property Name="ServerName">DestinationServer</Property>
       <Property Name="DatabaseName">BAMStarSchema</Property>
      </DeploymentUnit>
      <DeploymentUnit Name="Alert">
       <Property Name="DBServer">DestinationServer</Property>
       <Property Name="InstanceDatabaseName">BAMAlerts</Property>
      </DeploymentUnit>

    It seems that the shipped shcemas and logic does not take into consideration the 1st and 2nd databases.
    Regarding the 3rd, the scripts has taken into consideration 2 other DBs, "BAMAlertsApplication" and "BAMAlertsNSMain". "Alet" by iteself doesn't exist, also the script assumes that this "Alert" DB is an instance  (="InstanceDatabaseName").

    Thanks for your help
    Friday, January 15, 2010 5:22 PM