Monthly I have to generate a new snapshot for replication and reinitialize subscriptions in SQL 2005. I would really like to automate and schedule this, but I'm not having much luck discovering how.

Not being a DBA I'm pretty much poking around blindly in there, and I definitely don't want to screw anything up. Any thoughts?

  • What type or replication is it? – SuperCoolMoss Oct 15 '09 at 11:41

It would help if we knew what kind of replication it was & if it was a push or pull subscription. I'm going to make some massive assumptions here & say that your using the following, which i think the the most common replication setup:

  • transactional replication
  • push subscription
  • distributor running at the publisher

1. Create a schedule to create the database snapshots

If you look at the SQL Agent jobs at the distributor you should see a job that's named something like this:
In addition, it will be in a category called REPEL-Snapshot.

When you setup replication you would have been given an option to run the snapshot on a schedule. If you chose to do so, you should see this under the schedules, otherwise you'll probably see a one-off schedule in there that's not enabled from the last time you ran a snapshot. Leave this schedule alone and create another one to your liking.

2. Re-initalize subscriptions

This will re-initalize a publication for all subscribers. You can create a sql agent job to run this whenever you like. For more details check msdn.

exec sp_reinitsubscription @publication = 'publication_name', @subscriber = 'all'
| improve this answer | |
  • Close on your assumptions. Good show. It wasn't schedule initially because I wanted manual control, but I didn't know (or remember) the agent thing. – Scott C. Reynolds Oct 16 '09 at 12:28

I'm not familiar with replication, so keep that in mind, but if you see a script button in the top left of dialogs, you can save that script and then schedule in a job.

If the script function is not available, you could resort to tracing the activity of SSMS to see what it's doing - then working through that and the documentation to create your script.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.