I am working on implementing an ETL process to load data into a warehouse.

Part of this process is pulling data from our production server (Server P) to a staging server where the actual load into the warehouse (and the warehouse itself) is located (Server S). We are using snapshot replication for this.

For QC and some other purposes, I need a way to determine the date that the snapshot was taken on Server P, from Server S. Linking servers for this query I don't think is an option.

I'm hoping that there is a record in a metadata table somewhere to reflect this, or some other command I can run to query the snapshot history.


MSSnapshot_History has snapshot information for replication.

You can join to MSSnapshot_Agents to narrow it down to your specific agent, publication, and job.

These tables are found in the [distribution] database on your distribution instance.

Here's a basic query to get the last start time of your snapshot agents.

select a.name, max(start_time) laststart
    from MSsnapshot_agents a
        join MSsnapshot_history h 
            on a.id = h.agent_id
            where h.runstatus = 2 -- this will get you the last successful execution
        group by a.name
  • So this needs to run on the distributor but not necessarily on the subscriber if they are not the same, correct? – JNK Jan 28 '13 at 19:55
  • @JNK yes, sorry. I updated with a bit about where they're found. – swasheck Jan 28 '13 at 19:56
  • OK, and just to confirm, the start_time field is the timestamp of the snapshot in the publisher, correct? – JNK Jan 28 '13 at 19:58
  • It is the start time of the agent job. This information is logged by the distribution instance (usually the subscriber). – swasheck Jan 28 '13 at 20:05
  • So in fact this will show me the time that the distributor agent ran, but NOT the time that the snapshot was taken on the publisher – JNK Jan 28 '13 at 20:35

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.