SQL Server Performance

restored a db but store procedures are missing

Discussion in 'ALL SQL SERVER QUESTIONS' started by WingSzeto, Sep 5, 2012.

  1. WingSzeto Member

    We are on SQL 2008 R2 standard 64 bit. Recently I performed a full backup on a database using native SQL backup tool from a server and restored it to another different physical server. For some reason, the restored database includes all the user defined stored procedures as expected but doesn't retain stored procedures belonging to replication process. For example, any sp with the names like sp_MSdel_dboxxxxxxx, sp_MSins_dboxxxxxxxx, etc don't exist in the restored db, which they exist in the source database I did the backup from. The source database is a subscriber database in our transactional push replication environment and that's why it contains all these sp_MSxxxxxx procedures.

    For all these years I have been working with SQL server, I always thought that a full restore will produce the same database as the source database. This problem puzzles me. If anyone can shred some light on this, it would be very much appreciated.

    w
  2. ghemant Moderator

  3. WingSzeto Member

    Ghemant,

    Thanks for the suggestion. This does bring back the replication stored procedures. I have a follow-up question. I am aware of that 'with Keep_replication' option but I haven't needed to use it for all the restore I have done in the past. Part of the reasons is that I thought this option is for retoring replication settings for 'publisher' database . Here I am restoring just subscriber databases. Our subscriber databases are on a different SQL server machine than the publisher db's.

    Furhermore, when the problem of replication stored procedures not showing up after the restore arised, I was restoring a total of 4 subdscriber databases. All restored databases except one retain the replication stored procedures without the need of using that option. I don't quite understand why the problem only happened for one particular db out of four. Is there some setting that would set this 'With Keep_replication' option on/off on a database behind the scene that I am not aware of it being set already?

    W
  4. ghemant Moderator

Share This Page