Raul


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

I don't disagree that its the only option that would (mostly) result in a usable system. Its not what (my interpretation) Kevin is asking for though.


Additionally, I'm still struggling to think of a valid use case for this process. Either the table alterations were what you wanted so you keep on or they weren't so you roll back. I'm hoping that Kevin can enlighten me.

Roy

Roy

The solution that Raul suggested make sense because it gets the database into a known state.

Another way to implement this is to just drop the whole database, recreate it and then do the restore. My expectation is that a restore (with catalog) will get me a database in the _exact same_ structure as was backed up. Even if it means it wipes out some data.

A couple of use cases:
* During development. I'll write update scripts for the next version of our software (add columns, change column type, etc). I make a backup of the database.  When I mess something up, I'd like to do a simple restore to get the database back to the same state. This use case doesn't even to have to skip tables, the restore with catalog will alter the catalog and I'll have an unusable database.

* Production: We have several log files that we use that we don't regularly back up. Usually because they are very large or simply throw away data. So, a customer backs up (skipping those tables). Something happens at the customer (e.g. 'playing with data', malicious actor, etc) and the customer wants to restore. Again, the database will be unusable.

There are also use cases of where restoring with catalog does not update the catalog tables that were not included in the backup. This would be less of an expectation for me, but maybe a nice-to-have.

In the same Dev scenario above. Maybe I'd like some 'dev logs' to remain.

Kevin

Kevin

Kevin

>The solution that Raul suggested make sense because it gets the database into a known state.

I agree it works, but it wasn't what you were asking for.

>Another way to implement this is to just drop the whole database, recreate it and then do the restore. My expectation is that a restore (with catalog) will get me a database in the _exact same_ structure as was backed up. Even if it means it wipes out some data.

There I have to disagree. You are deliberately excluding a table so in effect saying just ignore the data in this one but because of the INCLUDE CATALOG clause you ask backup to get the structure. Following restore again including the catalog you have data which is incompatible with the structure. Exactly what I'd expect. Tim could change it so that any tables ignored are left out of the catalog but apart from simple systems it would also need to deal with triggers, sql functions and RI (might be other stuff as well). I have no doubt Tim would be capable of the technical challenge but wether he should take it up is another matter.

>A couple of use cases:
>* During development. I'll write update scripts for the next version of our software (add columns, change column type, etc). I make a backup of the database. When I mess something up, I'd like to do a simple restore to get the database back to the same state. This use case doesn't even to have to skip tables, the restore with catalog will alter the catalog and I'll have an unusable database.

I'm sorry to be brutal but it sounds as though you need to sort out your workflow. You need to backup a known good state and restore to that known good state as and when you screw up. Especially in development. I've learnt that the hard way.

>* Production: We have several log files that we use that we don't regularly back up. Usually because they are very large or simply throw away data. So, a customer backs up (skipping those tables). Something happens at the customer (e.g. 'playing with data', malicious actor, etc) and the customer wants to restore. Again, the database will be unusable.

Two simple solutions:

1. If you're changing the structure of the log files between backups insist the customer backups the system immediately before and immediately after a structure change
2. Put the log files in a separate database

>There are also use cases of where restoring with catalog does not update the catalog tables that were not included in the backup. This would be less of an expectation for me, but maybe a nice-to-have.
>
>In the same Dev scenario above. Maybe I'd like some 'dev logs' to remain.

Do not understand the above two.

Roy

Roy,

I understand that I will have to change my backup/restore implementations. I can also see that my original question implied I was looking for a _specific resolution_ to the issue that I encountered.

All I'm looking for is a way that, when using a RESTORE with a subset of tables + catalog, it does not corrupt the database. I understand that you don't feel that this is a corruption, but the database does become unusable; regardless of the use case.

Would it be possible to prevent this type of restore if the catalogs don't match; just like a full restore does? (This is not a feature request, just throwing it out there).

Thanks for everyone's help on this.

Kevin

On 5/17/2018 8:29 AM, Kevin Koehne wrote:
> All I'm looking for is a way that, when using a RESTORE with a subset of tables + catalog, it does not corrupt the database. I understand that you don't feel that this is a corruption, but the database does become unusable; regardless of the use case.

I would suggest to submit this to Tim (thru elevatesoft support) as
feature enhancement - he will see it here as well.

i personally think this would be a nice to have feature - optional
command as part of restore (RESET EXCLUDED TABLES) which is only
applicable if catalog is included

i.e.
RESTORE DATABASE abx FROM xyz
IN STORE a
TABLES table1,table2,table3
INCLUDE CATALOG
RESET EXCLUDED TABLES

> Would it be possible to prevent this type of restore if the catalogs don't match; just like a full restore does? (This is not a feature request, just throwing it out there).

That would be another option

Raul

Kevin


>I understand that I will have to change my backup/restore implementations. I can also see that my original question implied I was looking for a _specific resolution_ to the issue that I encountered.

OK sorry for being so snotty. Raul has given you a solution however, the easiest solution (since you don't need to know which tables were excluded) is to drop the entire database before doing a restore - after all if you're doing a restore you don't want what's there anyway. You could do this with a simple script or 2 session.execute statements.

Depending on the environment I might be tempted to close the database, use an API command to rename its directory and then create a blank directory with the same name then do the restore. Have a question - was the restore successful and if answered yes then delete the copied directory.

Its also worth considering moving things like log files into a separate directory so that the can just sit and grow quietly. If you have tables where the structure is liable to change and tables that are going to be very stable it could be worth separating those. It makes backing up / restoring a bit more complex but means more chance of recoving stuff when the inevitable happens.

>All I'm looking for is a way that, when using a RESTORE with a subset of tables + catalog, it does not corrupt the database. I understand that you don't feel that this is a corruption, but the database does become unusable; regardless of the use case.

I just see a difference between "its corrupted" and "please mess this up for me"

>Would it be possible to prevent this type of restore if the catalogs don't match; just like a full restore does? (This is not a feature request, just throwing it out there).

That I quite like, but the suggestion I'd make is a command along the lines of COMPARE CATALOG [filename][CURRENT] WITH [filename] which would give a list of differences. It would then be down to the developer to do something about it (I suggest a temper tantrum and stamping feet).

I'm suggesting this because an overly rigorous comparison between catalog and table in the early days of ElevateDB made it hell.

Roy

Raul


>i personally think this would be a nice to have feature - optional
>command as part of restore (RESET EXCLUDED TABLES) which is only
>applicable if catalog is included
>
>i.e.
>RESTORE DATABASE abx FROM xyz
>IN STORE a
>TABLES table1,table2,table3
>INCLUDE CATALOG
>RESET EXCLUDED TABLES

I disagree, as I posted above you need to add one command - drop database and job done - same effect, no new code needed, under programmers control.

Without trying it I just realised I'm not sure if you need A CREATE DATABASE as well

Roy

Kevin,

<< 3) Modify the table that was not backed up >>

By "modify", do you mean ALTER TABLE ?

Tim Young
Elevate Software
www.elevatesoft.com

Kevin,

<< The database is unusable and irreparable; not a great state to be in >>

Just to clarify: the *database* is fine, there is just an issue with opening the tables that contain invalid metadata tags relative to the current database catalog that you've restored.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

>>Just to clarify: the *database* is fine, there is just an issue with opening the tables that contain invalid metadata tags relative to the current database catalog that you've restored.<<
True - it's the tables not the db.


>>By "modify", do you mean ALTER TABLE ?<<
Yes, altered a column.

Kevin