I'm doing a partial backup of our database, and including the catalog. If I modify one of the tables that has been excluded, and then do a restore (including catalog), the excluded table becomes corrupt.

I've attached a set of statements to reproduce the issue. (You'll probably have to run the various steps individually, I didn't spend any time creating a script

Basically the steps are:
1) create the database, with 2 sample tables
2) Backup one of the tables (including catalog)
3) Modify the table that was not backed up
4) Restore the backup (including catalog)
5) The ignored table is now corrupt. With the message: "ElevateDB Error #100 There is an error in the metadata for the table TestIgnore (The internal structure does not match the structure in the catalog)"

I'm assuming that the corruption is because the table definition was overwritten, but the table data structure was not changed, because it was not restored.

Could the restore of the catalog skip those tables that are not being restored?

Thanks,
Kevin

Kevin


I think what you're asking for would be possible but awkward, and probably introduce other problems.

What happens if you try restoring the backup without the catalog? Without testing that should work and is there for exactly the scenario you have.


Roy Lambert

Thanks Roy. I will try without including the catalog.

However, I do think that restoring with the catalog makes sense. When doing a restore with the catalog, I'd expect the database to be in the exact state as when I backed up - and the excluded tables would be empty. This would make the operation consistent and predictable.

Also - what happens when I do a restore without the catalog, but the destination table has changed? In a quick test, the restore fails because the table definition changed. This makes sense to me because without the catalog, I'd expect there to have to be an exact match in definitions.

I'd at least expect the tables not to be corrupted when restoring with the catalog

Kevin

Kevin

>However, I do think that restoring with the catalog makes sense. When doing a restore with the catalog, I'd expect the database to be in the exact state as when I backed up - and the excluded tables would be empty. This would make the operation consistent and predictable.

Not really. Think that if you had a function or trigger defined which operated on a column which you've now altered or dropped. Think of potential impact on referential integrity.

>Also - what happens when I do a restore without the catalog, but the destination table has changed? In a quick test, the restore fails because the table definition changed. This makes sense to me because without the catalog, I'd expect there to have to be an exact match in definitions.

This is where you're meant to be responsible for your own application. You know in advance that you're going to change the structure. You have to manage the process correctly.  I'm not sure under what circumstances I would want to back up the bit I'm not altering, change something and then restore the unaltered bits. I'm sure there will be a brilliant use case but its not something I'd ever do. My process goes:

1. backup (often including a sideways copy)
2. make changes
3. test / use
4. if everythings OK make new backup otherwise restore and go back to step 1

>I'd at least expect the tables not to be corrupted when restoring with the catalog

They're not corrupted they're just not what the catalog expects. OK it looks like corrupted and works like corrupted but its not really corrupted.

Roy

Roy,
>Think of potential impact on referential integrity.<
Yep, I understand that's an issue. But there are already statements that can mess with RI (see Empty Table t Ignore Constraints). I shouldn't be _not_ backing up tables that will affect RI (sorry for the double negative).

>This is where you're meant to be responsible for your own application<
Yep, I'd know that my RI is affected.

>They're not corrupted they're just not what the catalog expects. OK it looks like corrupted and works like corrupted but its not really corrupted.<
The database is unusable and irreparable; not a great state to be in

Kevin

On 5/15/2018 9:46 AM, Kevin Koehne wrote:
> The database is unusable and irreparable; not a great state to be in

For what it's worth as a short term workaround you should be able to
just delete the ignored table files from disk (after restore) and then
EDB will re-create them as per catalog when you access them 1st time.

Not cleanest way though.

Raul

Thanks for the suggestion Raul.

Raul


That won't work. Kevin has altered the structure of a table, ignored it in backup but included the catalog. He now wants to restore from backup including the catalog and wants ElevateDB to merge the two catalogs together.

Your suggestion would result in the altered table going back to the structure it had at backup prior to its alteration.

Roy Lambert

Kevin


>>Think of potential impact on referential integrity.<
>Yep, I understand that's an issue. But there are already statements that can mess with RI (see Empty Table t Ignore Constraints). I shouldn't be _not_ backing up tables that will affect RI (sorry for the double negative).

Not sure just what you're saying. The point of a backup is to provide something to fall back to if a major problem occurs. Having had the pleasure resolving RI issues without restoring from backup (I had a tape backup subsystem at the time and none of the tapes would restore) can be a nightmare.

>>This is where you're meant to be responsible for your own application<
>Yep, I'd know that my RI is affected.
>
>>They're not corrupted they're just not what the catalog expects. OK it looks like corrupted and works like corrupted but its not really corrupted.<
>The database is unusable and irreparable; not a great state to be in

Its not irreparable - use Raul's suggestion. It won't do what you want but it will get things back to before you started alterations.

I'm still interested if there's a real use case for this.

Roy

On 5/16/2018 3:06 AM, Roy Lambert wrote:
> That won't work. Kevin has altered the structure of a table, ignored it in backup but included the catalog. He now wants to restore from backup including the catalog and wants ElevateDB to merge the two catalogs together.
> Your suggestion would result in the altered table going back to the structure it had at backup prior to its alteration.

Yes. However, end result of this IMHO is the only valid result one
should expect - ignored tables removed so they get recreated as per
catalog.

Raul