question

Magnus Ahlkvist avatar image
Magnus Ahlkvist asked

DBCC Clonedatabase - duplicate key in sys.sysclsobjs

Hi! I'm trying to run DBCC CLONEDATABASE to create a clone of one of our user databases. I get error message that the command is trying to insert duplicate values in sys.sysclsobjs, with key value (50,5). I opened an admin Connection to the instance and checked what's going on, and saw class 50 seems to be schema names, and the row with ID=5 seems to be a named schema for a Windows user. There are no duplicate rows in sys.sysclsobjs in the source database used with the DBCC CLONEDATABASE command. Any ideas?
sql-server-2016dbcc-clonedatabase
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
EDIT I found that my theory is not 100% correct. Even if the schema_name is the same in model and the user database to clone, I still get the duplicate key error. So any user created schema in model causes DBCC CLONEDATABASE to fail. /EDIT I have found what causes this error. I have created schemas in the model database. It looks like DBCC CLONEDATABASE creates those schemas also in the clone. It looks at the user database schemas. If the schema_id and name is the same, we don't have a problem. But if there's a schema in model, with the same schema_id as Another schema in the user database I'm cloning, there's a conflict, and DBCC CLONEDATABASE tries to create both the schemas found in model (with the same schema_id they have in model) and the schemas found in the user database I'm cloning (also with the same schema_id). I have in this scenario a schema_id=5 for a certain schema in the model database, and a schema_id=5 for a schema with Another name in the user database to clone. When I drop the schema with schema_id=5 in the model database, the DBCC CLONEDATABASE-command works fine.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Great catch.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.