Get a listing of snapshot data files

I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot. I'm having trouble locating where the snapshot files are located from within a query.

Essentially we have several environments and they are not consistent on where they place these files so I need my code to be smart enough to put these files back where they were.

Just to clarify: When I say the snapshot files I mean the .ss files.

You can identify database snapshots in sys.databases by the column source_database_id being not null.

Join it to sys.master_files and you're done:

SELECT mf.database_id,
    dbs.source_database_id,
    mf.type_desc,
    mf.name, 
    mf.physical_name
FROM sys.master_files AS mf
INNER JOIN sys.databases AS dbs
    ON mf.database_id = dbs.database_id
WHERE source_database_id IS NOT NULL

In case you need to filter for one specific source database, you can add that predicate to the WHERE clause.

StackExchange Database Administrators Q#134965, answer score: 7