Post reply

DBCC CLONES does not include system objects

  • January 31, 2020 at 12:00 pm

    #3721963

    Hello.

    I have a database and some of the tables are included in transactional replication. The SQL Server version is SQL 2014 SP3.

    I created a database clone using 'DBCC CLONEDATABASE' command like this:

    DBCC CLONEDATABASE ('MyDatabase', 'MyDatabase_Clone')

    GO

    The database clone was created succesfully, but if I launch the next command:

    select * from sys.sql_expression_dependencies

    I get the next error:

    Msg 0, Level 11, State 0, Line 0

    A severe error occurred on the current command.  The results, if any, should be discarded.

    I have been analyzing this error reasons and I have seen that the issue is because the DBCC CLONEDATABASE does not included system objects that exists in the original database (tables, views etc ...). More exactly all the system objects related with replication are missing (for example sysarticles).

    ¿Can anyone have the same issue and can help my to solve it?

    Thanks in advance.

     

     

     

  • January 31, 2020 at 12:10 pm

    #3721968

    you would not want to create the replication objects (ok it's fairly safe as the SQL agent job won't be there to pollute your distributor) - but not all users will be using DBCC clone that way. I think the DBCC command was written with "safety first" in mind... a first on our platform... no wait... DBCC was originally a Sybase command that originates from pre-sql 6.5 🙂

    Use Redgate sql compare to move the objects over - or just script them in management studio

    I'm also fairly confident that replication came in way after DBCC and nobody wants to mess with DBCC

    MVDBA

  • January 31, 2020 at 12:28 pm

    #3721971

    Hello.

    Thanks for your answer.

    I only want to have a database backup without data in an easy way. I think that DBCC DATABASE may be a great and easy way to do that.

    Using a database clone I can then compare it with Redgate SQL Compare using as source my cloned database unless to use the original/full database. The error I have published in the original post is because Redgate SQL Compare gets the information from sys.sql_expression_dependencies and this have invalid information (because the system objects are empty in the cloned database).

    ¿Do you or somebody know if there is an easy way to have a database backup with no data to uses as source to compare with Redgate SQL Compare?

    Thanks.

    Regards.

  • January 31, 2020 at 12:40 pm

    #3721976

    a few options  I can think of

    1. use Redgate from the start - SQL compare to a blank database, or check it all in via source control and then build from GIT/TFS or wheatever - I use replication all the time an Redgate has never let me down - I suspect DBCC clone is not your friend
    2.  script out your replication setup (distributor and publications, just don't do the subscribers) and add that to the process at whatever point you think is correct
    3. have  look at redgate's sql provisioning

    MVDBA

  • January 31, 2020 at 1:10 pm

    #3721980

    Thanks Mike for your suggestions.

    Regards

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply