Post reply

Missing stored procedures when backing up subscriber in transactional replication

  • September 12, 2007 at 1:51 pm

    #176586

    We utilize Transactional replication to replicate a subset of tables.

    Part of our backup procedure involves backing up both the publisher db and subscriber db.

    Under SQL 2000 this is not a problem.

    Under SQL 2005 when a backup of the subscriber database is restored it is missing all of the sp_MSupd_, sp_MSins_, sp_MSdel_ stored procedures.

    Is this intended behavior? I can't find any information about this anywhere.

  • September 12, 2007 at 2:31 pm

    #733273

    It is expected. Just make sure that when you restore you specify KEEP_REPLICATION

    In BOL:

    Database Settings and Restoring

    During a restore, most of the database options that are settable using ALTER DATABASE are reset to the values in force at the time of the end of backup. If a replicated database is restored to a server other than the one on which the backup was taken, the KEEP_REPLICATION option must be used to retain the replication-related values in force at the time of the end of backup. For more information, see Backing Up and Restoring Replicated Databases.

    Note:

    This behavior differs from versions of SQL Server before SQL Server 2000.


    * Noel

  • September 12, 2007 at 2:32 pm

    #733274

    Note it should have said before 2005


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply