Cleaning the Destination Environment

If the restore job encounters error conditions that cannot be resolved, clean the destination environment so that it can start from an empty environment. Running the stored procedure sp_LogShippingClean located in the master database on the destination SQL Server instance will “clean” the destination environment. This procedure drops all databases and deletes the last restored data set for the specified source.

Before running this procedure, disable the BTS Log Shipping - Restore Databases job (the get backup history job may continue running). For more information about disabling the BTS Log Shipping - Restore Databases job see How to Restore Databases in the Backup BizTalk Server Job. After the sp_LogShippingClean procedure is run, the next time the restore job runs it will find the most recent full backup set with a valid subsequent log backup set. If this set has already been restored, the restore job clears the Restored column for the set and all subsequent sets and then proceeds with restoring the set.

Because the job looks for the most recent full backup set after the environment has been cleaned, force a full backup on the source system after running this procedure but before running the restore job.

The sp_LogShippingClean procedure must be repeated on all servers that are restoring databases for a given source system in order to keep the different servers in synch with each other in terms of which sets have been applied.

To run the sp_LogShippingClean procedure, connect to the master database on all SQL Server instances that are part of the disaster recovery site and execute the following command in SQL Server 2000 Query Analyzer or the New Query option available in SQL Server 2005 SQL Management Studio for each SQL Server instance:

sp_LogShippingClean 'SourceID'

where SourceID corresponds to the identifier configured on the production SQL Server instances.