x

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?

more ▼

asked Feb 16 at 09:01 AM in Default

avatar image

Magnus Ahlkvist
22k 20 41 42

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 20 at 08:06 AM

avatar image

Magnus Ahlkvist
22k 20 41 42

Great catch.

Feb 20 at 01:17 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x19
x1

asked: Feb 16 at 09:01 AM

Seen: 57 times

Last Updated: Feb 21 at 07:57 AM

Copyright 2017 Redgate Software. Privacy Policy