Share via

MSSQL Server Replication Agent jobs disappearing

Chamara Ranathunga 21 Reputation points
2022-10-10T09:18:37.55+00:00

Hi,

I have configured merge replication with 07 different subscribers. I wanted to have a backup database of a subscriber database.
So, I took a full backup of the subscriber database and restored that in a new database on the same server. after restoring the database, I observed the replication is not working and found out that all agent jobs for replication are missing in the server.
However, I managed to re-create the agent jobs from the script of another subscriber. But, I need to do the same for the other 06 subscribers.
How can I get a backup from the subscriber database and restore that in a new database on the same server without affecting the merge replication of the agent jobs?

Thanks and appreciate your response.

Regards
Chamara

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2022-10-10T15:07:04.463+00:00

    SQL Agent jobs are stored in the msdb database, not the target database.

    Also, you cannot simply backup and restore a replicated database and have it work. You must reestablish replication in the restored database.

    See:
    https://learn.microsoft.com/en-us/sql/relational-databases/replication/administration/back-up-and-restore-replicated-databases?view=sql-server-ver16

  2. YufeiShao-msft 7,091 Reputation points
    2022-10-11T08:20:57.26+00:00

    Hi @Chamara Ranathunga ,

    If you restore a backup of a backup a replicated database to another database, replication settings cannot be preserved, you can always keep a current script of your replication settings in a safe location, it can be used to help recreate your replication settings

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. Shivam Kumar 541 Reputation points
    2022-10-13T20:09:06.72+00:00

    Hi @Chamara Ranathunga

    The jobs are probably disappearing due to the reason already listed by @YufeiShao-msft
    There are special considerations that you have to keep in mind when dealing with Backups and Restores of databases which are participating in replication.

    Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.

    Source: back-up-and-restore-replicated-databases

    And as you are restoring the replicated database to another database(as you are creating a copy with another name) your replication settings are not preserved and as replication depends on agent jobs , non preserving thing might be triggering the removal of the replication specific agent jobs.

    You can get more information about what's happening with the jobs from sql agent error log (not sql server log but sql agent log)check the current as well as one before the current one and see if there is anything getting logged there?

    Work around would be to break the replication for this database on the subscriber where you are taking the backup and restore it as copy before setting the replication and then reestablish the replication for the original database between publisher and this subscriber or restore the backup on another instance and then backup it up there and use that backup to create copy on subscriber with restore as another name.

    Regards,
    Shivam

    0 comments