Sql-server – Run SQL Server Snapshot Agent automatically

replicationsql-server-2008

I am trying to setup automatic transactional replication. Basically I have some script files that are executed after each other. They set up a replication from one server to another and this works just fine. Only problem is, that it is never starts. If I look into the Snapshot-Agents status, I can see that it has never been run before. If I just click start in that dialogue, everything works as expected afterwards. But I don't want the user to have to Open the sql server studio and activate the snapshot agent manually.

What I already tried:

  • running the snapshot.exe with all params set. It seems to work fine, but afterwards I get an error in my replicationmonitor that something from a path could not be read, but there arent any settings for a path, so I don't know how to fix this.

  • exec msdb.dbo.sp_start_job .. here I tried some params but I have no clue what to put here. My best guess was @job_name='myreplication' but I just get an error that it cant be found.

What is the easiest way to automate this?

Best Answer

I believe you are looking for sp_startpublication_snapshot. You'll want to execute this at the Publisher on the publication database.

Regarding the error: from a path could not be read, you'll want to ensure the Snapshot Agent process account has sufficient permissions to generate the snapshot. The process account needs the following permissions per BOL:

  • At minimum, be a member of the db_owner fixed database role in the distribution database.
  • Have write permissions on the snapshot share.
  • The account that is used to connect to the Publisher must at minimum be a member of the db_owner fixed database role in the publication database.

This is covered in Replication Agent Security Model.

Related Question