SQL Replication - Snapshot agent

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi!

The snapshot agent is not suppose to run.

This is what we do for the SQL replication for very big database size.

1. A full DB backup from the Prod DB was done a few days back.
2. On the same server we will create a publication of the DB and set the properties to allow initialization of backup files.
3. We will the create a differential backup of the same DB.
4. Then on the destination server, we will do a full restore of the DB but with norecovery
5. We will also restore back the differential backup of the DB
6. From the Prod DB, we will create a subscription script for the destination server
7. After the script is executed, the SQL replication starts.

The above process is done but without running the snapshot agent due to its large database size.

The issue is I accidentally click the snapshot agent to start when there is no need for it to run at all.

In the other DBs, the snapshot agent's status is "never started".

So I would like to reverse the activation of the snapshot agent so that there is no error message about it.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi!

The snapshot agent is not needed but accidentally started and so it is giving unnecessary errors.

I am replicating from the Prod server to another server that is to manage all the DB replication so that users only access one server instead of multiple servers to get the report.

The Prod servers are only replicating 2 Articles.

ASKER

Hi! Pushpakumara Mahagamage,

I am following exactly the instructions from your link.

But I accidentally ran the snapshot agent, so it there anyway to reverse it nack or do I need to do the whole replication with the sql database restore again?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

The .MDF size varies from 200GB to 13TB.

The secondary DB is to consolidate all the DBs together so that users do not have to login to multiple servers / clusters to do view their reports.

They are just login into one server and they can see all the DBs at once.

My aim is to disable the snapshot agent as it keeps reporting the 10min error message.

If I can set it back to never run then there will be no errors or white crosses with red background

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.