How can I determine the date of a snapshot in SQL Server 2008r2?

Question

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.

Answer 1

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