3

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Lumpy
  • 2,129
  • 9
  • 34
  • 43

1 Answers1

7

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.

spaghettidba
  • 11,376
  • 31
  • 42