Hi all.

A customer of mine, yesterday, performed a bad processing with our PB application on a SQL Anywhere 10 database and unintentionally deleted a lot of data in various tables. He has no backup of the db (not to mention) but the database has the complete log since creation.

I tried to recover the data acting like this:

transform the log into SQL with dbtran; edit the obtained SQL file (9GB) and remove all yesterday's operations (checkpoint dates can help me); create a brand new db with dbinit and execute the modified SQL file on it, obtaining the "restored" db. I was able to carry out the first two steps without problems; at the third, however, the execution stops me because the SQL file contains strange statements such as:

load into table DBA.rscri from '\\\\.\\pipe\\2792-ASANP_table\ ...

Seems it's trying to import data from a "pipe" which obviously does not exist, blocking everything.

Do you know how to overcome this problem? Are there any alternative solutions to recover customer data?

Thank you so much, Luca

asked 06 Jan, 11:08

lucaarena's gravatar image

lucaarena
414
accept rate: 0%

When using LOAD TABLE, "inserts are not recorded in the transaction log file unless WITH ROW LOGGING clause is specified, so the inserted rows may not be recovered in the event of a failure depending upon the logging type. The original file is required if you must recover the rows and WITH FILE NAME LOGGING is used."

(07 Jan, 11:12) Chris Keating
Replies hidden

But would a normal, not logged LOAD TABLE use a pipe as source? I would have expected a file path (and hopefully, that file does still exist...).

(07 Jan, 12:27) Volker Barth

A LOAD TABLE can use any valid source that is specified in the statement. I have encountered many times in my career with SQL Anywhere where LOAD TABLE was used and the source files no longer were available. Even if those source files were on a file path.

(07 Jan, 12:52) Chris Keating
Replies hidden

Yes, but I guess using a pipe explicitly is very advanced, and as stated by the OP, was apparently used internally by DBUNLOAD.

(07 Jan, 13:35) Volker Barth
Comment Text Removed
Comment Text Removed

I guess the LOAD TABLE statements have been generated by DBUNLOAD some years ago when I upgraded the db by using DBUNLOAD -ac (infact there's no real filename in the statement, but a "pipe"). I didn't know that SQL Anywhere does not log the "real" INSERT statements but just the LOAD TABLE statements, which are totally useless since of course I don't have the old db anymore.

My customer has been lucky since I found an old backup of the same database and was able to apply the remainder of the log on it... but if I had to recover it just from the log it would have been impossible.

permanent link

answered 07 Jan, 12:54

lucaarena's gravatar image

lucaarena
414
accept rate: 0%

> SQL Anywhere does not log the "real" INSERT statements but just the LOAD TABLE statement

Suprisingly, that fact wasn't clearly documented until Version 7:

V7 Help - LOAD TABLE - Side effects - Inserts are not recorded in the log file.

The implications were made clear in Version 10:

V10 Help - LOAD TABLE - Side effects - Inserts are not recorded in the log file. Thus, the inserted rows may not be recovered in the event of a failure.

The process of "bulk loading", also called "non-transactional" or "non-logged", is common... and commonly known to be dangerous... there is no such thing a as a free lunch, and the transaction log file is an expensive feature when it comes to performance... compare the performance of DELETE versus TRUNCATE TABLE on a million row table, for example.

(07 Jan, 15:01) Breck Carter
Replies hidden

Hm, I do not have the docs anymore (or more precisely, can't open the format...) but I guess even für v5.5 it was told that LOAD TABLE data would not be replicated via SQL Remote because it did not lead to inserts in the transaction log...

(08 Jan, 03:16) Volker Barth

> even für v5.5 it was told that LOAD TABLE data would not be replicated via SQL Remote because it did not lead to inserts in the transaction log

Nope... I just did a text search on the 16 *.HTML files in the V5.5 Help that contain "load table" and there is no mention of the transaction log at all... perhaps it was a trade secret back then, the fact that LOAD TABLE is fast because it doesn't use the log :)

Seriously, both SQL Remote and LOAD TABLE were new so it's not surprising nobody stumbled upon that problem before the docs were written.

(08 Jan, 06:47) Breck Carter

Well, I certainly used both LOAD TABLE and SQL Remote then (and really long with a third-party software) but made sure I wasn't loading into published tables:) - Thanks for checking the older docs.

(08 Jan, 08:18) Volker Barth

Did you use LOAD TABLE and SQL Remote before the V5.5 Help was written? :)

(08 Jan, 14:45) Breck Carter

LOL, no, I certainly read the documentation before using SQL Remote productively. Well, in those times, one could really read through all those paper books... I guess that ceased with v6. :)

(08 Jan, 17:05) Volker Barth
showing 1 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×16

question asked: 06 Jan, 11:08

question was seen: 103 times

last updated: 08 Jan, 17:06