Sql-server – DBCC CLONEDATABASE missing schemas

sql serversql-server-2016

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.

Best Answer

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.

Related Question