2

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?

1
  • What type or replication is it? Oct 15, 2009 at 11:41

2 Answers 2

1

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:
servername-dbname-publicationname-1
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'
1
  • 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. Oct 16, 2009 at 12:28
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .