Friday, August 26, 2016

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


4 comments:

Anonymous said...

Interesting.. When going back I always expect the road to be rough
and the bridges to start catching fire.

A few versions ago I remember me upgrading an empty database and
then comparing the unload/reload files of both versions with a
diff-tool to identify the stuff that needs to be cut out manually
for a downgrade.

Any special reasons for you to go back?

I myself haven't upgraded to 17 so far, because I found some
incompatibilities with dblib.dll when reading INOUT parameters from stored procedures.
This one will keep me on 16 for a while.

Other than that I haven't run into problems with 17 so far.
Profiling and SQL Central in general are some massive improvements in 17.

Breck Carter said...

A large client asked the question after being disappointed in Version 17 performance even after installing an EBF that was supposed to fix the problems. The article was the result of a "preliminary investigation" into the downgrade technique ( it's tempting to call it "degrade" but that is actually the opposite of the desired effect: the purpose of the downgrade would be to improve performance, not degrade it :)

Leonardo Albino said...

Can perform backup image xxx.1
I do the scjview
by command type Dbbackup?
because Dbbackup makes .db and .log
I wanted to do everything in one file
you tell me if it is possible?
Best regards

Breck Carter said...

The BACKUP DATABASE TO statement can be used to create a single "archive backup" file. The RESTORE DATABASE statement must be used to restore from that backup.

The dbbackup utility can only be used to create separate "image backup" files. Those files can be directly started as a database with no RESTORE statement required.