0

I'm trying to run the following line of code:

DBCC CLONEDATABASE ([DBName],[DBName_Staging]) WITH VERIFY_CLONEDB

And I'm getting the following error:

Msg 2601, Level 14, State 1, Line 15 Cannot insert duplicate key row in object 'sys.syssingleobjrefs' with unique index 'clst'. The duplicate key value is (5, 51, 0).

I'm running on Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)

From what I've read a possible solution is deleting the duplicate row from model. I know how to do it, I'm just not sure if it's safe, or if this is the best solution.

Any suggestions?

Based the query in this sqlskills article the "duplicate" objects in model are objects like queue_messages_99999999, syscommittab, filestream_tombstone_99999999, etc. I am trying to create copies of 30+ databases so moving/removing the objects in the user database side is complicated at best. Is it safe to remove these system objects?

Improve this question
2
  • DBCC CHECKDB on the source DB?
    – Charlieface
    Jul 27 at 22:57
  • @Charlieface good thought but it comes up clean.
    – Kenneth Fisher
    Jul 28 at 2:24

1 Answer 1

Reset to default
4

I've tested this on SQL Server 2019 Developer Edition and it seems like DBCC CLONEDATABASE doesn't support creation of clone if there are any user objects created in the model database.

DBCC CLONEDATABASE doesn't support creation of a clone if there are any user objects (tables, indexes, schemas, roles, and so on) that were created in the model database. If user objects are present in the model database, the database clone fails with following error message: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object with unique index 'index name'. The duplicate key value is

You should probably drop the objects in model database before doing CLONEDATABASE command.

you can reproduce it by creating a test table on your source database and then create a similar table on model. Then on a new window execute the DBCC CLONEDATABASE command.

Improve this answer
2
  • Yeah - sometimes I see folks put sp_whoisactive or other stored procedures in the model database so it’ll be “inherited” by any new database created. But even adding stored procedures to model can lead to conflicts down the road in dbcc clonedatabase.
    – sqL_handLe
    Jul 28 at 6:11
  • Unfortunately in this case I can't find any non-system objects. There are some of the service broker objects but otherwise it's all system objects.
    – Kenneth Fisher
    Jul 28 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.