Tuesday, May 27, 2008

Using Advantage Online Backup from Visual Studio

With the release of Advantage 8.0 a new Online (hot) Backup feature was added. This functionality allows you to create a backup of your data while the data is in use. It creates a snapshot of the data and copies it to the specified location. You can get a detailed overview of Online Backup in the Advantage Help File.

Backups can be performed on free tables as well as data dictionaries. To backup a data dictionary you must have the proper permissions. For Advantage 8.x you must connect as ADSSYS, Advantage 9.0 includes new database roles which allow you to grant backup privileges to other users. Online backups are only available when using the Advantage Database (Remote) Server not the Advantage Local Server.

There are several ways to perform an Online Backup including; Advantage Data Architect (ARC), command line tool and a system procedure. ARC provides a graphical interface for creating a backup of your data. It is accessed by right-clicking on a connection in the repository and choosing backup from the context menu. The ARC backup utility also generates the proper SQL for calling the backup system procedure and a command line syntax for the adsbackup utility. The adsbackup utility is a command line tool for creating backups. It can be added to the Windows Scheduler to provide backups on a scheduled basis.

To perform backups programmatically you must use the sp_BackupDatabase or sp_BackupFreeTables system procedures. You must connect to the server using the ADSSYS account (version 8.x) or a member of the DB:Backup group (version 9). Then execute the stored procedure using an EXECUTE PROCEDURE SQL command. The procedure will return a resultset containing any errors and warnings from the backup. If an empty resultset is returned then no errors occurred during the backup.

There is a simple Visual Studio project available on Code Central which demonstrates how to call these procedures and view the results. The example allows the user to select a data and backup path. There is an Advanced tab which allows them to specify additional options. A screenshot of the main form is below.

Application Screen Shot

The code for setting up the syntax for sp_BackupDatabase or sp_BackupFreeTables is very straight forward. To get the results of the backup you can use either a DataReader or a DataAdapter. I choose to use a DataAdapter since it can be used with the DataGridView control to display the results. The DataReader is forward only and would involve writing more code to display the results. Remember to set the command timeout to 0 (no timeout) prior to executing the command otherwise the operation will timeout in 30 seconds. Examples of the command text and execution of the command are below.

   1: // Setup the command based on the table type
   2: if (rbDataDictionary.Checked)
   3: {
   4:     cmAds.CommandText = "EXECUTE PROCEDURE sp_BackupDatabase('" +
   5:                         txtBackupPath.Text + "', '" +
   6:                         sOptions + "')";
   7: }
   8: else
   9: {
  10:     cmAds.CommandText = "EXECUTE PROCEDURE sp_BackupFreeTables('" +
  11:                         txtDataPath.Text + "', '" + 
  12:                         txtSourceMask.Text + "', '" +
  13:                         txtBackupPath.Text + "', '" +
  14:                         sOptions + "', '" +
  15:                         txtPassword.Text + "')";
  16: }
   1: // Run the backup command
   2: try
   3: {
   4:     daAds = new AdsDataAdapter(cmAds);
   6:     // Put the results of the backup into a dataset
   7:     dsResults = new DataSet();
   8:     daAds.Fill(dsResults);
   9: }
  10: catch (AdsException aex)
  11: {
  12:     MessageBox.Show("Error: " + aex.Message);
  13:     lblStatus.Text = "Advantage Error";
  14:     ShowButtons(true);
  15:     return;
  16: }

Finally we will check to see if there are any rows were returned by the system procedure. If there are then we will give the user an option to view the results.

   1: // If the dataset contains rows an error has occurred during backup
   2: if (dsResults.Tables[0].Rows.Count > 0)
   3: {
   4:     lblStatus.Text = "Backup completed with errors";
   5:     btnViewErrors.Visible = true;
   6: }
   7: else
   8:     lblStatus.Text = "Backup completed successfully";

The example application also provides restore functionality and can be downloaded from Code Central on the Advantage DevZone. The project is called "Backup Example" and can be found under tools Tools or C#/VB.NET categories.

No comments: