Missing constraint after backup and restore

Question

Missing constraint after backup and restore

gdf2020

Old Hand

Points: 334
More actions
September 22, 2020 at 9:53 am

#3790026
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.

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

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gdf2020 Old Hand Points: 334 More actions · Answer 2

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.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gdf2020 Old Hand Points: 334 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

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.

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning