5

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.

1 Answer 1

5

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
5
  • So this needs to run on the distributor but not necessarily on the subscriber if they are not the same, correct?
    – JNK
    Commented Jan 28, 2013 at 19:55
  • @JNK yes, sorry. I updated with a bit about where they're found.
    – swasheck
    Commented Jan 28, 2013 at 19:56
  • OK, and just to confirm, the start_time field is the timestamp of the snapshot in the publisher, correct?
    – JNK
    Commented Jan 28, 2013 at 19:58
  • It is the start time of the agent job. This information is logged by the distribution instance (usually the subscriber).
    – swasheck
    Commented Jan 28, 2013 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
    Commented Jan 28, 2013 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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