August 3, 2016 at 9:23 pm
Comments posted to this topic are about the item Identifying Cloned Databases
August 3, 2016 at 11:44 pm
This was removed by the editor as SPAM
August 4, 2016 at 2:07 am
Oh no - finger trouble twice in two days, so no points again.
Aargh....
August 4, 2016 at 5:42 am
I learned something new from this, so thank you. That's always the best way to start the day.
August 4, 2016 at 6:48 am
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
August 4, 2016 at 7:35 am
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.
August 4, 2016 at 7:52 am
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)
August 4, 2016 at 8:06 am
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... 🙂
August 4, 2016 at 8:42 am
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
August 4, 2016 at 8:45 am
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.
August 4, 2016 at 12:43 pm
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
August 4, 2016 at 12:52 pm
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.
August 8, 2016 at 1:55 am
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