Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
What type or replication is it? – SuperCoolMoss Oct 15 '09 at 11:41
up vote 1 down vote accepted

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'
share|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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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