Missing constraint after backup and restore

  • Hi we have this procedure to restore a db:

    1. On the prod db Generate a script of a db and than click on Tables after that click on advanced in Types of data to script select schema and data.
    2. We launch a ps script that use sqlcmd -E -S -Q on the db and than copy it on aws s3.
    3. On the restore db we launch a restore script that copy the .bak files from s3
    4. After that we launch a drop tables sql script that use DROP TABLE [dbo]. on the database
    5. we launch the generated script on point 1.
    6. we restore on another db the bak files manually using the restore and select Overwrite the existing database (WITH RELACE) and the checkbox Close existing connection to destination database

    Now the problem is that on the production db there are 400 constraints and primary key, and on the restored db there are only 350 constraint and primary key furthermore is missing Unique clustered index.

    Someone can point me where to check and what could be the issue. Thanks in advance.

     

  • First, are all the FKs enforced on the primary database? Is it possible that you have data problems and the scripts are disabled or built using WITH NOCHECK and your export/import process is skipping some of that? Or even, yes, there are 400 on the one db, but 50 of them are disabled?

    Next, this isn't a backup and a restore. Couldn't you just run a BACKUP command and a RESTORE command? That's a page-by-page copy of the database, data, and all constraints, intact, not a rebuild. If you're using AWS virtual machines, this is a trivial task. If you're using AWS RDS, follow these instructions. It's no longer trivial, but it's pretty darned easy and guaranteed, everything gets moved.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Hi @Grant Fritchey thanks for your reply, we are moving towards backup and restore on AWS RDS but in the meantime I need to use this procedure, can you tell me , how to check if all the FKs are enforced on the primary db, and if not how to enforce them. I also need as you said the query to check i some of them are disabled. Thanks.

  • I did a quick search and found this. It'll tell you if there's a disabled fk or one using WITH NO CHECK (untrusted). That should get you pretty far into why you're not seeing everything. You may need to compare the structures. It could be more than just foreign keys not making the leap.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Hi @Grant Fritchey thanks for your support, I tried the query but the result are empty, how it is possible?

    SELECT

    name AS 'Constraint',

    is_disabled,

    is_not_trusted

    FROM sys.foreign_keys

    WHERE is_disabled = 1;

    Regards.

  • gdf2020 wrote:

    Hi @Grant Fritchey thanks for your reply, we are moving towards backup and restore on AWS RDS but in the meantime I need to use this procedure, can you tell me , how to check if all the FKs are enforced on the primary db, and if not how to enforce them. I also need as you said the query to check i some of them are disabled. Thanks.

    The RDS version of backup and restore has some serious issues.  For example, look into how to restore a single database.  Surprise!

    You might also want to look into what the DBAs will have for privs... Surprise! again!

    Then, compare the cost of RDS to EC2.  SURPRISE!

    And if you ever think you want to change a database from the FULL recovery model to Bulk Logged or Simple?  SURPRISE, SURPRISE, SURPRISE!

    Then, there's the occasional downtime for upgrades that you can't really control that much.

    Oh... and you want to have the Developers edition on Dev and QA  instances to legally save on licensing costs?  SURPRISE!

     

    Heh... RDS... the "gift" that keeps on takin'.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • gdf2020 wrote:

    Hi @Grant Fritchey thanks for your support, I tried the query but the result are empty, how it is possible?

    SELECT

    name AS 'Constraint',

    is_disabled,

    is_not_trusted

    FROM sys.foreign_keys

    WHERE is_disabled = 1;

    Regards.

    You may not have any that are disabled. That's possible.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 7 posts - 1 through 7 (of 7 total)

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