Is this a SQL Provision Cloned Database?

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:

2018-07-02 17_02_58-SQLQuery1.sql - (local)_SQL2016.StackOverFlow (PLATO_Steve (69))_ - Microsoft SQ

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.