Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

version Need approach that retains contents (several schemas with tables/views), db has checkpoint events. Current approach produced dbname.db file in backup directory, but when recovered from it the sybase central folder for tables and views were empty (very bad). Current cron/bkup solution uses following, what is it missing please (I also welcome links to 'detailed' documentation, exhausted sybase doc).

. /opt/sybase/sqlanywhere11/bin64/
cd /home/sybase
DATEDIR=/database/REBUILD/$(date '+%A')
dbunload -v @encrypted_conn.txt -an "/database/REBUILD/mytxn.db" -ap 4096 -ea None -
echo -e "`date`\n`hostname`" | mail -s "unload completed"
mv /database/REBUILD/mytxn.db $DATEDIR

The In-memory config follows:

-x tcpip(PORT=nnnn)
-gn 300
-o /database/mytxn_serverlog.log
-on 10m
-gk all
-gt 4
#-c 256m
-n mytxndb21
-ti 0
#-dt /database/mytxn01
-im c
-gc 20
-gr 2
-zo /database/mytxn/rlog.txt
-n mytxndb21

asked 28 Jan '11, 01:13

Cindy's gravatar image

accept rate: 0%

edited 28 Jan '11, 01:37

Graeme%20Perrow's gravatar image

Graeme Perrow

Can you elaborate more what you are trying to achieve, and what the problem is? As to your question's text, you seem to do a backup (which seems to give errors when started) whereas the script seems to do a database unload. That leaves me somewhat puzzled...

(28 Jan '11, 20:37) Volker Barth

@Cindy: From your answer, I've learnt about the usage of UNLOAD as backup means for in-memory mode databases. Thanks for un-puzzling...:)

(28 Jan '11, 22:31) Volker Barth

This solution does work as a backup to an -im c (checkpoint) database, the problem was simple, the full path to the encrypted_conn.txt was missing which produced a mytxn.db result that had a large byte size but was empty of schema data. For anyone who needs a in-memory db backup solution, the contents in original thread will work for you, just remember to fully qualify paths. Thank you to those that took the time to review. The reason you see an 'unload' solution to backup the database, it is because in-memory databases cannot use standard backup commands/solutions, as documented here:

Because changes are never written to the original database files, if a persistent copy of current database contents is required, you must use the dbunload utility or the UNLOAD TABLE statement. You can also use SQL queries to retrieve the changes, but you must then manually write these changes to the database file.

permanent link

answered 28 Jan '11, 22:18

Cindy's gravatar image

accept rate: 0%

edited 28 Jan '11, 22:28

Volker%20Barth's gravatar image

Volker Barth

In dev environment the unload produces a mydb.db that can recover db by copying the file to the database directory. But when unload is used in the prod envir and the resulting mydb.db file produced by the unload is copied to a failover server it is empty of the tables and views, etc. Can an unload be used on a database with connections be empty, and an unload used on a database not being used produce desired mydb.db with all the contents? Trying to figure out why unload works in dev IM but not in Prod IM. servers/db's are twins as well as the commands used for the unload. bkup sol needed.

(04 Feb '11, 22:48) Cindy

Maybe better to say a restore of prod IM db is needed. It can never be shutdown or connections stopped. A checkpoint event runs every 20 min.

(04 Feb '11, 22:50) Cindy

The documentation you quoted only applies to "-im nw". Backups with "-im c" work correctly.


permanent link

answered 31 Jan '11, 14:51

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

edited 31 Jan '11, 16:09

Comment Text Removed

@John: I guess I understand why dbbackup would be successful with "-im c" and not with "-im nw". (And as such, IMHO Cindy should be able to use backup). - Nevertheless, the quoted paragraph of the "Remarks" section of the documentation is not currently related to "-im nw" only. Something to improve/clarify?

(31 Jan '11, 21:16) Volker Barth

Yes, the doc needs to be improved. I added a comment on dcx.

(31 Jan '11, 23:12) John Smirnios

The unload produces a dbname.db file, no evidence yet to indicate not a complete/valid backup. BACKUP produces serveral backup part files. Seems easier to recover by just copying the dbname.db file produced by an unload. Are there any pro's/con's to using the unload vs BACKUP? Pretty sure I researched unload due to BACKUP throwing no transaction log errors, I'll restest if time permits or if you say unload is bad idea.

(02 Feb '11, 17:00) Cindy

Sorry about not having a definitive answer on how to backup an IM -c database. I have implemented several ways in the last year without consistent results, if anyone has a solution on how to backup an IM -c database that cannot be shutdown, always has connections with intensive writes/deletes, and has the DBA password changed regulary without shutdown's afterward (changing of passwords is not realized internally till after the DB is shutdown so encounter issues with internal operations passing/expecting old passwords-must be wired in catalog someplace not updated till after a bounce). The dbunload will only contain objects like tables and views if the database didn't have connections at the time of the dbunload statement. Unfortunately I'm unable to implement a consistent backup plan using backup database or dbunload for an IM -c database that will produce consistent recovery results. I welcome a solution that will work even when there are connections at the time of the backup, the database cannot be shutdown, and the DBA password is changed every 30 days without a DB bounce following the password change. The original, dbunload, solution seems to only work if there are no active connections at the time the cron fires. A code response for the backup/restore is preferred over text description of solution or a link to documentation text.

permanent link

answered 07 Feb '11, 15:32

Cindy's gravatar image

accept rate: 0%

What particular problem did you encounter with dbbackup and can you post a reproducible case here? I'm also not sure what you meant by the 'backup part files' that you mentioned in your previous comment.

(07 Feb '11, 21:11) John Smirnios

And if you have to struggle with ever-changing DBA passwords, it would be easier to run the BACKUP/UNLOAD from within the database and not as an client app (which has to authenticate each time). E.g. you could put the BACKUP/UNLOAD statements inside an event and have that run on a predefined schedule. I guess the built-in maintenance plan support might be a good starting point.

(08 Feb '11, 09:20) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 28 Jan '11, 01:13

question was seen: 3,007 times

last updated: 07 Feb '11, 15:32