Change behaviur for $(DefaultDataPath) or add new SQLCMD variable that reflects the location of the primary ile group
The default script options for creating additional files and filegroups relies on the $(DefaultDataPath) variable. This value is given by the server.
But: if you are using SQL Clone, using this value, you get orphaned files when the clone is either dropped or reset.
If you place the files within the vhd of the clone however, reset and dropping the clone works as expected.
Fridthjof-G Eriksen shared this idea
Fridthjof-G Eriksen commented
related to that, a sqlcmd variable that can tell you if a database is a clone or not would be helpful as well (we currently read the extended property from the database to determine this).