Restoring Broken SQL Server 2005 Transaction Replication without deletes

• DoctorIce

  Valued Member

  Points: 57

  More actions

  January 12, 2011 at 3:29 pm

  #230041

  Hi fellow Warriors!

  I have an interesting scenario:

  We have a production SQL Server 2005 database table than has data for 6 years.

  We would like to set up transactional replication and create a copy to be used for historical reporting.

  We will then disable replication of "delete statements" on the Publisher, and then truncate the production database table down and have data for only the last 2 years (this will speed up performance).

  However, the historical table at the subscriber will maintain the data for 6 years, and any inserts, or updates will still be replicated to the subscriber table. The only thing is that deletes will not be replicated.

  I have set this up and it all works well.

  My concern is as follows:

  How would we recover in case the subscriber database table (with historical data) crashes. The common strategies wont work, because we cannot use the publisher to re-generate it using a snapshot. Remember, the production data table has 2 years of data and the crashed subscriber table had data for 6 years.

  Is it possible to restore a back up copy of the subscriber database(historical) and then re-establish the same transactional replication link (no deletes) with the existing production database at the subscriber?

  If so, how can this be done ?

  Any suggestions or alternative strategies will be appreciated!

  Thanks - and more power to you all !

  Doctor Ice, Senior Database Engineer

• Rob Schripsema

  SSCertifiable

  Points: 7469

  More actions

  October 3, 2011 at 10:54 am

  #1389686

  Conrald,

  I see you never received a reply to your question. I am in a similar situation and am wondering if you came up with a good solution to this.

  Or...does anyone else have any experience with doing this?

  Thanks!

  Rob Schripsema
  Propack, Inc.