Identifying Cloned Databases

  • Comments posted to this topic are about the item Identifying Cloned Databases

  • This was removed by the editor as SPAM

  • Preparing us all for Redgate's latest product, "SQL-Dolly"?, sorry, "SQL-Clone"?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Oh no - finger trouble twice in two days, so no points again.

    Aargh....

  • I learned something new from this, so thank you. That's always the best way to start the day.

  • I think the question should have specified SQL Server 2014 SP2 and noted that it applies in no other release.

    There's a connect item asking for the feature to be inluded in SQL Server 2016, but that's still active, neither closed because they've done it nor closed because they are not going to - in fact no response at all from Microsoft in the 8 months since the item was raised, so maybe a few more votes for this would be helpful - please vote it up if you think it's a good idea.

    Neither of the recent BOL documentation for DATABASEPROPERTYEX (sql server 2012 version, or sql server 2008 to current + Azure version updated about 3 weeks ago) mentions the new parameter, and if that parameter is used with SQL Server 2016 it returns NULL (meaning Input Not Valid) not 1 (yes it's a clone) or 0 (no it's not a clone) so clearly the "correct" answer is wrong for SQL Server 2016.

    So those of us who use SQL Server versions other than SQL Server 2014 SP2 have to clone databases using the good old methods that have been available since the year dot (well, about 10 years ago) documented in [kb 914288] or on something similar. And we rely on some convention like adding "_clone" to the name (some MS documentation suggests adding "Test" to the name for clones) and are not going to get this question right as it stands.

    Tom

  • TomThomson (8/4/2016)


    I think the question should have specified SQL Server 2014 SP2 and noted that it applies in no other release.

    There's a connect item asking for the feature to be inluded in SQL Server 2016, but that's still active, neither closed because they've done it nor closed because they are not going to - in fact no response at all from Microsoft in the 8 months since the item was raised, so maybe a few more votes for this would be helpful - please vote it up if you think it's a good idea.

    Neither of the recent BOL documentation for DATABASEPROPERTYEX (sql server 2012 version, or sql server 2008 to current + Azure version updated about 3 weeks ago) mentions the new parameter, and if that parameter is used with SQL Server 2016 it returns NULL (meaning Input Not Valid) not 1 (yes it's a clone) or 0 (no it's not a clone) so clearly the "correct" answer is wrong for SQL Server 2016.

    So those of us who use SQL Server versions other than SQL Server 2014 SP2 have to clone databases using the good old methods that have been available since the year dot (well, about 10 years ago) documented in [kb 914288] or on something similar. And we rely on some convention like adding "_clone" to the name (some MS documentation suggests adding "Test" to the name for clones) and are not going to get this question right as it stands.

    Thanks for the clarification Tom. I thought I was going crazy as I could not see that documented on MSDN.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Interesting question, thanks Steve. But I would like to note that the result is not correct,

    because DATABASEPROPERTYEX('dbname', 'IsClone') will return NULL if the database

    is generated by using DBCC CLONEDATABASE or even does not exist. Probably Micro$oft will

    fix it. Please see below:

    USE TestDB

    GO

    SELECT @@VERSION AS SQLServer_Version;

    DBCC CLONEDATABASE (TestDB, TestDB_clone);

    GO

    SELECT DATABASEPROPERTYEX ('TestDB', 'IsClone') as TestDB_PROPERTYEX_IsClone;

    SELECT DATABASEPROPERTYEX ('TestDB_clone', 'IsClone') as TestDB_clone_PROPERTYEX_IsClone;

    SELECT DATABASEPROPERTYEX ('NotExistsDB', 'IsClone') as NotExistsDB_PROPERTYEX_IsClone;

    Results:

    --------

    SQLServer_Version

    -----------------------------------------------------------------------------

    Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

    Jun 17 2016 19:14:09

    Copyright (c) Microsoft Corporation

    Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )

    (1 row(s) affected)

    Database cloning for 'TestDB' has started with target as 'TestDB_clone'.

    Database cloning for 'TestDB' has finished. Cloned database is 'TestDB_clone'.

    Database 'TestDB_clone' is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    TestDB_PROPERTYEX_IsClone

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------

    0

    (1 row(s) affected)

    TestDB_clone_PROPERTYEX_IsClone

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------

    1

    (1 row(s) affected)

    NotExistsDB_PROPERTYEX_IsClone

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    NULL

    (1 row(s) affected)

  • I'm sorry, after several run the script everything is correct, which is seen from the results,

    that I'm sent from above. I don't even want to believe... 🙂

  • select @@VERSION

    Microsoft SQL Server 2016 (RC3) - 13.0.1400.361 (X64) Apr 9 2016 01:59:22 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 8.1 Pro 6.3 <X64> (Build 9600: ) (Hypervisor)

    DBCC CLONEDATABASE (AdventureWorks2014, AdventureWorks2014_Clone);

    GO

    [font="Courier New"]

    Msg 2526, Level 16, State 3, Line 3

    Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.[/font]

    Checking this https://support.microsoft.com/en-us/kb/3177838 tells nothing...

    Igor Micev,My blog: www.igormicev.com

  • ThomasRushton (8/4/2016)


    Preparing us all for Redgate's latest product, "SQL-Dolly"?, sorry, "SQL-Clone"?

    no, wrote this the same day I saw the DBCC command appear.

    I try not to do product questions.

  • TomThomson (8/4/2016)


    I think the question should have specified SQL Server 2014 SP2 and noted that it applies in no other release.

    You are right. Corrected.

  • Steve Jones - SSC Editor (8/4/2016)


    TomThomson (8/4/2016)


    I think the question should have specified SQL Server 2014 SP2 and noted that it applies in no other release.

    You are right. Corrected.

    The correction isn't quite correct - it says "and later", which implies it's in the version of SQL Server 2016 last released. The change to DATABASEPROPERTYEX certainly didn't make it into that; I heven't checked whether the DBCC function is there or not, but I doubt it as Microsoft still haven't responded to the connect item.

    Tom

  • TomThomson (8/4/2016)


    Steve Jones - SSC Editor (8/4/2016)


    TomThomson (8/4/2016)


    I think the question should have specified SQL Server 2014 SP2 and noted that it applies in no other release.

    You are right. Corrected.

    The correction isn't quite correct - it says "and later", which implies it's in the version of SQL Server 2016 last released. The change to DATABASEPROPERTYEX certainly didn't make it into that; I heven't checked whether the DBCC function is there or not, but I doubt it as Microsoft still haven't responded to the connect item.

    It's correct if you read "and later" as applying to the service pack/update level and not the version. This is how they talk in the Java world, for example. I don't agree with it either, but apparently it's a thing.

  • Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply