Downgrading A Database From SQL Anywhere 17 to 16

Question: How do I switch to SQL Anywhere 16 after upgrading my database to version 17?

Answer: The bad news is, there's no dbunload -downgrade option.

The good news is, it might not be very difficult, at least according to a preliminary test using the SQL Anywhere 17 demo database:

• Step 1. Start the current database with V17 dbsrv17.exe
  

• Step 2. Unload the current database with V17 dbunload.exe
  

• Step 3. Copy the V17 reload file to the V16 folder
  

• Step 4. Manually edit the V16 reload file
  

• Step 5. Create the new database with V16 dbinit.exe
  

• Step 6. Start the new database with V16 dbsrv16.exe
  

• Step 7. Open an ISQL session with V16 dbisql.com
  

• Step 8. Run the edited V16 reload file in ISQL
  

• Repeat as required, possibly starting at Step 4
  

"Can I try this at home?"

This demo does not address the following questions...

1. "How do I undo the changes I had to make when upgrading to SQL Anywhere 17?"
   

2. "Will the new objects (e.g., roles) cause problems in SQL Anywhere 16?"
   

3. "Will the column statistics work properly?"
   

4. "What about the MobiLink stuff?"
   
   "Have I forgotten anything? Oh, yeah..."
   

5. "Will this work for my giant production database?"
   

Here's the batch file used for testing, followed by some comments and another batch file to clean up before starting over:

ECHO OFF
REM  ******************************** 
ECHO Step 0. Set up folders and files
C:
CD C:\
MD TEMP
CD C:\TEMP
MD data
CD C:\TEMP\data
MD V16
MD V17
COPY /B /V /Y "%SQLANYSAMP17%\demo.db" "C:\TEMP\data\V17\demo.db"
COPY /B /V /Y "%SQLANYSAMP17%\demo.log" "C:\TEMP\data\V17\demo.log"
PAUSE

REM  ******************************************************* 
ECHO Step 1. Start the current database with V17 dbsrv17.exe
"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -n demo17^
  -o "C:\TEMP\data\V17\dbsrv17_log_demo17.txt"^
  "C:\TEMP\data\V17\demo.db"
PAUSE

REM  ********************************************************* 
ECHO Step 2. Unload the current database with V17 dbunload.exe
"%SQLANY17%\Bin64\dbunload.exe"^
  -c "ENG=demo17; DBN=demo; UID=dba; PWD=sql;"^
  -r "C:\TEMP\data\V17\reload17.sql"^
  -up^
  "C:\TEMP\data\V17\data17"
PAUSE

REM  ************************************************** 
ECHO Step 3. Copy the V17 reload file to the V16 folder
COPY /V /Y "C:\TEMP\data\V17\reload17.sql" "C:\TEMP\data\V16\reload16.sql"
PAUSE

REM  ***************************************** 
ECHO Step 4. Manually edit the V16 reload file
ECHO [edit reload16.sql]
PAUSE

REM  *************************************************** 
ECHO Step 5. Create the new database with V16 dbinit.exe
"%SQLANY16%\bin64\dbinit.exe"^
  -p 4096^
  "C:\TEMP\data\V16\demo.db"
PAUSE

REM  *************************************************** 
ECHO Step 6. Start the new database with V16 dbsrv16.exe
"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -n demo16^
  -o "C:\TEMP\data\V16\dbsrV16_log_demo16.txt"^
  "C:\TEMP\data\V16\demo.db" 
PAUSE

REM  ************************************************ 
ECHO Step 7. Open an ISQL session with V16 dbisql.com
"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=demo16; DBN=demo; UID=dba; PWD=sql;"
PAUSE

REM  **********************************************
ECHO Step 8. Run the edited V16 reload file in ISQL
ECHO [copy, paste and run reload16.sql]
PAUSE

REM  *************************** 
ECHO Repeat as required
ECHO [you might be able to start at Step 4]
ECHO All done...
PAUSE

The dbunload -up option in Step 2 is new with SQL Anywhere 17:

New -up option for the Unload utility (dbunload) and the Extract utility (dbxtract) This option allows the unloading of passwords. There are behavior changes associated with its use. See UNLOAD utility, and Extract Utility (dbxtract). -up Unloads user passwords (hashed value). You do not need to specify this option if you are performing an unload with reload (-ac, -an, or -ar option).

If you don't specify dbunload -up in Step 2 the following text in the reload SQL file will stop Step 8 from working:

****************** WARNING ******************************* * * * This file contains user definitions with removed * * password values. * * It should not be used to create a new database. * * * ****************** WARNING *******************************

If you want to avoid this error in Step 8:

Could not execute statement. User ID 'SYS_RUN_PROFILER_ROLE' does not exist SQLCODE=-140, ODBC 3 State="28000" Line 157, column 1 You can continue executing or stop. GRANT DELETE ANY TABLE TO "SYS_RUN_PROFILER_ROLE" WITH NO ADMIN OPTION

...you will have to remove these lines in Step 4:

GRANT DELETE ANY TABLE TO "SYS_RUN_PROFILER_ROLE" WITH NO ADMIN OPTION go

...or you can just choose "Continue" after each error in Step 8 :)

The -p 4096 in Step 5 is the default... it's just a reminder that when you run the "Old School" method using separate dbunload and dbinit steps, you are on your own when it comes to dbinit options.

Here's the batch file to clean up after running the demo:

ECHO OFF
ECHO Are you sure you want to delete all the files?
PAUSE 

"%SQLANY16%\bin64\dbstop.exe"^
  -c "ENG=demo16; DBN=demo; UID=dba; PWD=sql;"^
  -y

"%SQLANY17%\bin64\dbstop.exe"^
  -c "ENG=demo17; DBN=demo; UID=dba; PWD=sql;"^
  -y

ECHO Wait until the databases are stopped, then
PAUSE 

RD /S /Q C:\TEMP\data\V16
RD /S /Q C:\TEMP\data\V17

ECHO All done...
PAUSE

---