We use DBCC CLONEDATABASE currently in SQL Server 2019 and have done in previous versions too. Having recently begun a project to migrate some servers up to SQL 2022 I’ve found that the use of CLONEDATABASE is no longer giving a “clone” and instead is making some assumptions about my data and implementing (badly) the LEDGER functionality.

Example:

Say my database has 4 tables, (Table_A, Table_B, Table_C and Table_D) all have the attributes USER_TABLE, NON_TEMPORAL_TABLE and NON_LEDGER_TABLE in sys.tables.

Running DBCC CLONEDATABASE creates two of these tables as ‘HISTORY_TABLE’ types (instead of NON_LEDGER_TABLE), with one being paired to another one of the tables. You can see this relationship as the object_id of say Table_B is present in the ‘history_table_id’ column of Table_A when looking at sys.tables. The same is true of Table_C and Table_D in my scenario.

It is also apparent that on some of the columns in Table_A and Table_C (the ones still created as NON_LEDGER_TABLE types) that the clone has chosen to add ‘GENERATED ALWAYS AS ROW END’ to some of the fields.

I can’t run SYSTEM_VERSIONING = OFF as I’m presented with the error that “SYSTEM_VERSIONING is not turned ON” for those tables. I can drop and manually re-create the Table_A & Table_C which removes the ‘history_table_id’ values, but I still can’t drop Table_B and Table_D as they are still seen as a ledger history table.

I have no idea why SQL 2022 has made these choices on my behalf and I can’t see to get around them, so for now at least I am having to script out the schema of my database and create it that way which leads to its own challenges and isn’t as easy as simply specifying ‘DBCC CLONEDATABASE’ in code.

​We use DBCC CLONEDATABASE currently in SQL Server 2019 and have done in previous versions too. Having recently begun a project to migrate some servers up to SQL 2022 I’ve found that the use of CLONEDATABASE is no longer giving a “clone” and instead is making some assumptions about my data and implementing (badly) the LEDGER functionality. Example:Say my database has 4 tables, (Table_A, Table_B, Table_C and Table_D) all have the attributes USER_TABLE, NON_TEMPORAL_TABLE and NON_LEDGER_TABLE in sys.tables. Running DBCC CLONEDATABASE creates two of these tables as ‘HISTORY_TABLE’ types (instead of NON_LEDGER_TABLE), with one being paired to another one of the tables. You can see this relationship as the object_id of say Table_B is present in the ‘history_table_id’ column of Table_A when looking at sys.tables. The same is true of Table_C and Table_D in my scenario. It is also apparent that on some of the columns in Table_A and Table_C (the ones still created as NON_LEDGER_TABLE types) that the clone has chosen to add ‘GENERATED ALWAYS AS ROW END’ to some of the fields. I can’t run SYSTEM_VERSIONING = OFF as I’m presented with the error that “SYSTEM_VERSIONING is not turned ON” for those tables. I can drop and manually re-create the Table_A & Table_C which removes the ‘history_table_id’ values, but I still can’t drop Table_B and Table_D as they are still seen as a ledger history table. I have no idea why SQL 2022 has made these choices on my behalf and I can’t see to get around them, so for now at least I am having to script out the schema of my database and create it that way which leads to its own challenges and isn’t as easy as simply specifying ‘DBCC CLONEDATABASE’ in code.    Read More

​