where did it go and how to get it back

[sparks]

I just saw something I don't understand
This happened to some (NOT ALL) of my FK

example I had 2 tables
client and clientprivateinfo

in client clientID is PK and in clientprivateinfo clientID is listed
as FK go to view and you have the nice little line with the references
showing.

I did a backup of the database and when I look now clientID is linked
to other tables with PK but clientprivateinfo no longer has a foreign
key and not linked to client table


1) I messed up something and lost the FK, I don't know how but I am
sure it was something I did.

2) is this fk and link stored in the DB and can it be restored
automatically?

if not I will have to make the clientid a FK again.
since this is something I am now seeing in over 10 tables this is
scary

[Jeroen Mostert]

Post by sparks
I just saw something I don't understand
This happened to some (NOT ALL) of my FK
example I had 2 tables
client and clientprivateinfo
in client clientID is PK and in clientprivateinfo clientID is listed
as FK go to view and you have the nice little line with the references
showing.
I did a backup of the database and when I look now clientID is linked
to other tables with PK but clientprivateinfo no longer has a foreign
key and not linked to client table
1) I messed up something and lost the FK, I don't know how but I am
sure it was something I did.

One possibility is that you changed the table from the designer in such a
way that it had to be recreated from scratch. Recent versions of Management
Studio no longer do this (they always generate scripts instead) but it may
have been possible, in older versions, to perform this change in such a way
that foreign keys are lost. Disclaimer: this may not be true and I may be
slandering Management Studio unfairly.

Best practice: don't modify tables from the designer (if you do). It's
barely adequate to create new tables with. Invest in scripts instead.

Post by sparks
2) is this fk and link stored in the DB and can it be restored
automatically?

Foreign keys are table metadata and therefore contained in a backup. Almost
everything pertaining to a database is contained in a backup; only a few
things that are considered belonging to the server (like the backup record
itself) are stored elsewhere.

You can't restore "just" the foreign key, if that's what you're asking -- a
restore always restores everything (with careful arrangements it's possible
to restore data piecemeal, but that's beyond the scope of this post). Data
will likewise be overwritten.

If this is not acceptable, what you can do is restore the database under a
different name and use Tasks -> Generate Scripts from Management Studio to
script all foreign keys. Execute the resulting script on the existing
database. All CREATE statements for keys that already exist will fail, but
the rest should go through. If you want to get really fancy, you can script
all keys from the existing database for DROP, execute the script on the new
database, then script all remaining keys for CREATE, so only the ones
missing in the original database will be scripted.

[sparks]

Thanks for the info I saved some scripts of everything the other day.
I have the original and a copy I am working in.
should I be able to get all primary and foreigns from the original and
apply them to the copy?

I better read up on this, MAKE A COPY of the copy and try there LOL


On Fri, 30 Mar 2012 19:42:30 +0200, Jeroen Mostert

Post by Jeroen Mostert

Post by sparks
I just saw something I don't understand
This happened to some (NOT ALL) of my FK
example I had 2 tables
client and clientprivateinfo
in client clientID is PK and in clientprivateinfo clientID is listed
as FK go to view and you have the nice little line with the references
showing.
I did a backup of the database and when I look now clientID is linked
to other tables with PK but clientprivateinfo no longer has a foreign
key and not linked to client table
1) I messed up something and lost the FK, I don't know how but I am
sure it was something I did.

One possibility is that you changed the table from the designer in such a
way that it had to be recreated from scratch. Recent versions of Management
Studio no longer do this (they always generate scripts instead) but it may
have been possible, in older versions, to perform this change in such a way
that foreign keys are lost. Disclaimer: this may not be true and I may be
slandering Management Studio unfairly.
Best practice: don't modify tables from the designer (if you do). It's
barely adequate to create new tables with. Invest in scripts instead.

Post by sparks
2) is this fk and link stored in the DB and can it be restored
automatically?

Foreign keys are table metadata and therefore contained in a backup. Almost
everything pertaining to a database is contained in a backup; only a few
things that are considered belonging to the server (like the backup record
itself) are stored elsewhere.
You can't restore "just" the foreign key, if that's what you're asking -- a
restore always restores everything (with careful arrangements it's possible
to restore data piecemeal, but that's beyond the scope of this post). Data
will likewise be overwritten.
If this is not acceptable, what you can do is restore the database under a
different name and use Tasks -> Generate Scripts from Management Studio to
script all foreign keys. Execute the resulting script on the existing
database. All CREATE statements for keys that already exist will fail, but
the rest should go through. If you want to get really fancy, you can script
all keys from the existing database for DROP, execute the script on the new
database, then script all remaining keys for CREATE, so only the ones
missing in the original database will be scripted.

[Jeroen Mostert]

On 2012-03-30 20:05, sparks wrote:
[losing keys]

Post by sparks
Thanks for the info I saved some scripts of everything the other day.
I have the original and a copy I am working in.
should I be able to get all primary and foreigns from the original and
apply them to the copy?

Yes, but two caveats:

- A foreign key to a table requires a primary key on that table, so make
sure the keys are scripted in order.

- Primary keys are often also the clustered index (the physical order of
rows on disk), and creating a clustered primary key involves rewriting the
entire table. For large tables, this takes time, and the table is
inaccessible while this is going on. If you have lots of missing
keys/indexes like this, it may pay off to just restore from backup and then
import the missing data back. Missing keys also means you can have
inconsistent data added in the meantime (duplicate keys or foreign rows
referring to non-existent parent rows), and if that's the case the keys
can't be recreated at all unless you fix the data first.

Post by sparks
I better read up on this, MAKE A COPY of the copy and try there LOL

If there is disk space, this is always a good idea. An even better idea is
to perform operations like the ones you apparently did only after you've
made a backup. :-) I assume your database isn't the one supplying a major
airline company with their daily reservations, but still.

[Erland Sommarskog]

Post by Jeroen Mostert
One possibility is that you changed the table from the designer in such
a way that it had to be recreated from scratch. Recent versions of
Management Studio no longer do this (they always generate scripts
instead) but it may have been possible, in older versions, to perform
this change in such a way that foreign keys are lost. Disclaimer: this
may not be true and I may be slandering Management Studio unfairly.

There is no absolutely no risk of unfair slander when it comes to the
Table Designer and modifying table through it. It's rotten from the
bottom up.

The only change is that in SSMS 2008, it will by default block if the change
leads to a table recreation, but you can remove this if you like to shoot
yourself in the foot.

One of the flaws in the change scripts is that the transaction scope
is incorrect, so if memory serve the foreign keys are restored in a
separate transction, so if this fails you could lose the f-keys.

Rather than applying the update directly, you can generate a script and
run that. But not that gives you even worse transction control, since if
a statement fails, the transaction may be rolled back and the rest of the
script is executed which also can wreck your database.