Rebuilding databases

Rebuilding a database is a specific type of import and export involving unloading and reloading your entire database. The rebuild (unload/load) and extract procedures are used to rebuild databases, to create new databases from part of an existing one, and to eliminate unused free pages.

If you are rebuilding your database to upgrade it to a newer version of SQL Anywhere, see Upgrading SQL Anywhere.

You can rebuild your database from Sybase Central or by using the dbunload utility.

Note

It is good practice to make backups of your database before rebuilding, especially if you choose to replace the original database with the rebuilt database.

For more information, see Backup and data recovery.

With importing and exporting, the destination of the data is either into your database or out of your database. Importing reads data into your database. Exporting writes data out of your database. Often the information is either coming from or going to another non-SQL Anywhere database.

If you specify the encryption options -ek, -ep, or -et, the LOAD TABLE statements in the reload.sql file must include the encryption key. Hard-coding the key compromises security, so a parameter in the reload.sql file specifies the encryption key. When you execute the reload.sql file with Interactive SQL, you must specify the encryption key as a parameter. If you do not specify the key in the READ statement, Interactive SQL prompts for the key. See Interactive SQL utility (dbisql).

Loading and unloading takes data and schema out of a SQL Anywhere database and then places the data and schema back into a SQL Anywhere database. The unloading procedure produces data files and a reload.sql file which contains table definitions required to recreate the table exactly. Running the reload.sql script recreates the tables and loads the data back into them.

Rebuilding a database can be a time-consuming operation, and can require a large amount of disk space. As well, the database is unavailable for use while being unloaded and reloaded. For these reasons, rebuilding a database is not advised in a production environment unless you have a definite goal in mind.

From one SQL Anywhere database to another

Rebuilding generally copies data out of a SQL Anywhere database and then reloads that data back into a SQL Anywhere database. Unloading and reloading are related since you usually perform both tasks, rather than just one or the other.

Rebuilding versus exporting

Rebuilding is different from exporting in that rebuilding exports and imports table definitions and schema in addition to the data. The unload portion of the rebuild process produces text format data files and a reload.sql file that contains table and other definitions. You can run the reload.sql script to recreate the tables and load the data into them.

For more information, see Internal versus external unloads and reloads.

Consider extracting a database (creating a new database from an old database) if you are using SQL Remote or MobiLink. See Extracting databases.

Rebuilding replicating databases

The procedure for rebuilding a database depends on whether the database is involved in replication or not. If the database is involved in replication, you must preserve the transaction log offsets across the operation, as the Message Agent and Replication Agent require this information. If the database is not involved in replication, the process is simpler.