Rebuilding version 10 and later databases

Rebuilding a database consists of unloading and reloading the database to upgrade its file format. When you upgrade the file format, it changes the format used to store and access data on disk, letting you use all the new features and performance enhancements in the latest version of the software.

Caution

Unloading and reloading a large database can be time consuming and can require a large amount of disk space. The process may require disk space approximately twice the size of your database to hold the unloaded data and the new database file.

If you are rebuilding a database that is a remote database in a MobiLink installation or that is involved in SQL Remote replication, and if you use the dbunload utility, you must be sure to use the -ar or -an option. These options ensure that the transaction log offsets for the new database are set to match those of the old database.

When using dbunload with a version 10 or later database, the version of dbunload used must match the version of the database server used to access the database. If an older version of dbunload is used with a newer database server, or vice versa, an error is returned.

Because of index changes in SQL Anywhere, when you rebuild a database by unloading and reloading it, the rebuilt database may be smaller than the original database. This decrease in database size does not indicate a problem or a loss of data.

Note

It is recommended that you back up your database before you rebuild it.

Carry out the standard precautions for upgrading software. See Important upgrade precautions.

Back up the database. For example:

dbbackup -c "DBF=mydb.db;UID=DBA;PWD=sql" old-db-backup-dir

For more information, see Backup quick start.

Choose Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.
Start a version 12 database server running the database you want to upgrade, and then connect to the database from Sybase Central.
Choose Tools » SQL Anywhere 12 » Unload Database.
Read the text on the first page of the Unload Database Wizard and then click Next.
Select Unload A Database Running On A Current Version Of The Server, and then select the database from the list. Click Next.
Choose to unload and reload into a new database. Click Next.
Specify a new file name for the database.
You can also specify the page size for the new database, but the page size you specify cannot be larger than the database server page size. The default page size is 4096 bytes. You can encrypt the database file if you want. If you choose strong encryption, you need the encryption key each time you want to start the database. Click Next.

For more information about database file encryption, see Encrypting and decrypting a database.
Choose Unload Structure And Data. You can also select any other options you want for your database. Click Next.
Choose Unload All Database Objects. Click Next.
Specify whether you want to connect to the new database when the unload/reload is complete.
Click Finish to start the process. You should examine the new database to confirm that the rebuild completed properly.

For more information about using the Unload Database Wizard, see Export data with the Unload Database Wizard.

To rebuild a database (command line)

Carry out the standard precautions for upgrading software. See Important upgrade precautions.
Ensure that you have exclusive access to the database to be upgraded and ensure that the path of the version 12 utilities is ahead of the path of the other utilities in your system path. See Using the utilities.
Run the Unload utility (dbunload) and use the -an option to create a new database.
dbunload -c "connection-string" -an new-db-file
The database user specified in the connection-string must have DBA authority on the database that is being rebuilt.

This command creates a new database. If you want to replace the existing database with an upgraded database, use the -ar option instead of -an. To use the -ar option, you must connect to a personal database server, or to a network database server on the same computer as the Unload utility (dbunload).

For information about other Unload utility (dbunload) options, see Unload utility (dbunload).
Shut down the database and archive the transaction log before using the reloaded database.

If you want to change the characteristics of the database during unload and reload (for example, change a case-sensitive database to a case-insensitive database), the procedure is more involved. See Rebuilding databases.

Discuss this page in DocCommentXchange.