How to restore a SQL Anywhere 9.0 archive backup (.db and .log) into a SQL Anywhere 16.0?

[Former Member]

I have tried reloading from the db file, but it shows me an error:

The database archive '/Users/betoesquivel/workspace/gmcapital/CADI/sapii.db' could not be restored on the server '_sc328961176'. Error during backup/restore: unable to open device /Users/betoesquivel/workspace/gmcapital/CADI/sapii.db (Unknown err status reading HDR labels) [Sybase][ODBC Driver][SQL Anywhere]Error during backup/restore: unable to open device
/Users/betoesquivel/workspace/gmcapital/CADI/sapii.db (Unknown err status reading HDR labels) SQLCODE: -697 SQLSTATE: HY000 SQL Statement: RESTORE DATABASE '/Users/betoesquivel/workspace/gmcapital/CADI/restored.db' FROM '/Users/betoesquivel/workspace/gmcapital/CADI/sapii.db'

I also tried unloading the db file, but it shows me this error:

Connecting and initializing
***** SQL error: Unable to start database server
An error occurred while attempting to unload the database '/Users/betoesquivel/workspace/gmcapital/CADI/sapii.db'.

The reason I need to do this reloading is that I want to be able to query and mess around with it in a test development environment (for data analysis and the likes). I have access to the server that is running the database, but I prefer not working on it since it is very important for the company I work for.

Any help will be appreciated. Thank you in advance.

[Breck_Carter]

The SQL Anywhere 16 version of the dbunload.exe utility must be used to upgrade a V9 database file to V16.

There are a couple of roadblocks to this process...

First, the V16 dbunload utility cannot connect to a running V9 database, so dbbackup must be used to make a copy of the V9 database.

Second, the V16 dbunload utility can read a V9 database file, but NOT on the same computer where dbeng9.exe or dbsrv9.exe is running, so the dbbackup copy must be created on (or moved to) a different computer from the running V9 database.

That means Step 2 below must be run on a different computer from the running V9 database.

It also means that either (a) Step 1 below is run on a different computer from the running V9 database, or (b) the copydir\\ddd9.db and copydir\\ddd9.log files must be moved to a different computer.

When the rules are broken, the error messages are confusing; for example:

SQL Anywhere Unload Utility Version 16.0.0.2052
Connecting and initializing
***** SQL error: Cannot access file 'unloadold.sql' -- No such file or directory

Either an old version of the server is running, or the database "ddd9" was start
ed in an old version of the server.  Please shut it down before running the unlo
ad.

---

Here's a sample of the Windows commands to upgrade a running V9 database to V16, using the "old school" technique of separate unload/dbinit/reload steps:

REM 1. Make a V9 backup in a subfolder.

"%ASANY9%\\win32\\dbbackup.exe"^
   -c "ENG=ddd9;DBN=ddd9;UID=dba;PWD=sql"^
   -y^
   copydir

REM 2. Use V16 to unload V9

"%SQLANY16%\\bin64\\dbunload.exe"^
   -c "DBF=copydir\\ddd9.db; UID=dba; PWD=sql"^
   -r reload.sql^
   -y^
   data

REM 3. Create a V16 database.

"%SQLANY16%\\bin64\\dbinit.exe"^
  ddd16.db

REM 4. Start the V16 database.

"%SQLANY16%\\bin64\\dbspawn.exe"^
  -f "%SQLANY16%\\bin64\\dbsrv16.exe"^
  ddd16.db 

REM 5. Load the schema and data.

"%SQLANY16%\\bin64\\dbisql.com"^
  -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql;"^
  READ ENCODING Cp1252 reload.sql

REM 6. Start an ISQL GUI session..

"%SQLANY16%\\bin64\\dbisql.com"^
  -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql"

PAUSE

[Former Member]

Ok, thanks for the great explanation. I know understand exactly what and how to do this. The problem is that when using V16 to unload my .db file I get a: SQL error: Unable to start database server.

Do you know if this error is a syntax error, or am I doing the backup wrong? How can I debug the .db file to find this sql error?

Thank you for all your help.

[Breck_Carter]

Make sure there are no other SQL Anywhere engines running on the computer where you are trying to run dbunload.

Please show us the exact dbunload command that you are running, when you get that message.

[Former Member]

These are the commands that I am running for the unloading of the db.

. /Applications/SQLAnywhere16/System/bin64/sa_config.sh

/Applications/SQLAnywhere16/System/bin64/dbunload -v -c "UID=dba;PWD=XXX;DBF=/Users/betoesquivel/workspace/gmcapital/BI/CADI/sapii_30.06.2015.db" -r "/Users/betoesquivel/workspace/gmcapital/BI/CADI/reload.sql" -n

[Breck_Carter]

Your use of dbunload -n profoundly changes the behavior, but nevertheless it should work.

C:\\projects\\$templates\\$SA_templates\\run\\dbunload\\upgrade_v9_to_16_running_datab
ase - Copy>REM 2. Use V16 to unload V9

C:\\projects\\$templates\\$SA_templates\\run\\dbunload\\upgrade_v9_to_16_running_datab
ase - Copy>"C:\\Program Files\\SQL Anywhere 16\\bin64\\dbunload.exe"   -v   -c "DBF=
copydir\\ddd9.db; UID=dba; PWD=sql"   -r reload.sql   -n
SQL Anywhere Unload Utility Version 16.0.0.2052
Connecting and initializing
Unloading user and group definitions
Unloading table definitions
Unloading index definitions
Unloading functions
Unloading view definitions
Unloading procedures
Unloading triggers
Unloading SQL Remote definitions
Unloading MobiLink definitions

C:\\projects\\$templates\\$SA_templates\\run\\dbunload\\upgrade_v9_to_16_running_datab
ase - Copy>PAUSE
Press any key to continue . . .

The error message may be different in the Unix version than in Windows.

Make sure the *.db file specification is correct. Make sure there are NO copies of ANY SQL Anywhere server running on the computer running dbunload... not version 9, not version 16, not version 12, not anything.

[Former Member]

No instances of SQL Anywhere running I ran the command ps in terminal and saw that there are no other instances of SQL Anywhere running on my machine.

Running file -I mydb.db results in the following: sapii.db: application/octet-stream; charset=binary

Questions Is the db file supposed to be in binary format? What is the .db file specification? Can my problem be that I am trying to open a MS DOS file on Mac OS X?

I am going to make another backup in the server today, and give that another try.

[Breck_Carter]

SQL Anywhere *.db files are binary portable across different operating systems and different computer architectures, even different endian machines.

When making a backup, make sure to create an "image backup" as shown by my dbbackup example, as opposed to an "archive backup" using the BACKUP DATABASE statement or the Sybase Central Backup Database Wizard.

[Breck_Carter]

Please, everyone, downvote this reply! :)

---

Stop doing complex things, and just (a) make a file copy of the original database .db and .log files then (b) start the .db file using dbsrv16.exe.

If you get an error, show us the command you tried to run.

[Former Member]

Is there an equivalent command in mac for dbsrv16.exe? Thanks.

[Former Member]

Oh, and can I make a copy of the db while the db server is on? Because I can't turn it off.

[former_SQLA_member1694880]

dbbackup.exe -c "commlinks=tcpip;eng=yourserver;dbn=yourbase;uid=dba;pwd=sql" -d -t -y c:\\db_backup

[Former Member]

I did the backup, and when I try to run the db on my computer with SQL Anywhere 16, I get a Database can't be started error. With this description: "db was created by a different version of the software. You must rebuild this database to use it with this version of SQL Anywhere."

[Breck_Carter]

My profound apologies! Version 10 databases are the earliest version that can be started using SQL Anywhere 16 without upgrading the *.db file first!

That means my reply is completely worthless, and I will try again...