DBCC CLONEDATABASE missing schemas

Question

I have used DBCC CLONEDATABASE to create a copy of a database without any data. I have now found that some schemas were not created, thus some objects have no schema. Its hard to reproduce this as it is database specific, i.e. I have cloned 9 successfully but 1 with this issue. All the schemas would be owned by dbo.

Because I can't get this reproduced, why were certain schemas not be created in the clone, would anyone have any idea what to check or if there is a way to debug the dbcc command?

I'm using SQL Server 2016 SP2 CU1.

The database was originally created before SQL Server 2005. If I go select name, schema_id, schema_name(schema_id) SchemaName, * From sys.objects in certain objects the schema name is null and the schema id isn't in sys.schemas. A few tables and synonyms are effected, and I can't twirl down either the tables node or synonyms node in object explorer of ssms (killed session error!). Also to clarify this is only an issue in the cloned database. I also tried recreating the synonym and transferring ownership of the table but I get an error saying the table can't be found.

Answer 1

Sounds like you had objects in SQL Server 2000, and their owner was dropped, leaving those objects "orphaned." When the database was upgraded to any version >= 2005, the "owner" was changed to a schema_id, but for those owners that had been dropped, that schema didn't exist.

To fix this, you can create a bunch of dummy schemas until you have one that correlates to each missing schema_id, and then run a statement like this for each object in each missing schema:

ALTER SCHEMA dbo TRANSFER dummy_schema.object_or_synonym;

The only complication might be if you have objects with the same name but different schema_id values; these will collide. Once you've sorted it all out, then you can drop all the dummy schemas.

At some point you might consider creating a new empty database in a modern version, copying your tables and data to it, and then getting rid of the old one. Might eliminate more surprises like this in the future.