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.


1 Answer 1


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,
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.


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.