As I work with SQL Provision, I keep finding new questions and concerns from clients and customers. Recently I had someone wonder if we could determine whether or not a database on which they were working was a SQL Clone cloned copy.
You can, and it’s easy to check. When SQL Clone creates a database, it will use the base image, and ensure there is an extended property set on the database itself.
The function sys.fn_listextendedproperty() is used to return the database extended properties. We can use the DEFAULT keyword for the various parameters, like this:
SELECT objtype ,
objname ,
name ,
value
FROM fn_listextendedproperty(DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
GO
This gives me an empty result set on a non-SQL Clone database if I have no extended properties set. If I had others set, I might get some result. For a database I’ve setup with SQL Provision, I’ll get this:
For the most part, I don’t care that I’m using a clone rather than a native SQL Server database, but there could be places I do care, and certainly I want to filter out this extended property from my version control system.
SQL Provision is a great tool for rapidly giving new environments to developers without the hassles of restoring copies and using lots of storage space. If you want to give it a try, download an evaluation today.