Database Backups

Overview: Backing Up a Progeny Database and its Transaction Log File

You can create a backup job either through the Progeny Desktop Client or through the Progeny Server Configuration Utility. You can have as many backup events as you like, but each event MUST have its own folder assigned: Instead of creating dozens of copies of the database and transaction log files, the events will overwrite the previous copy each time they run to minimize disk space. The Progeny Server Configuration Utility allows you to set ‘events’ which will automatically create a copy of the database and transaction log at the time the event runs.

To create an immediate backup

Creating an immediate backup of the Progeny database, without stopping the Progeny database, can be done using SQL Central. To accomplish this task, follow the steps below:

1. Open SQL Central and click on the Connect computer icon.
2. Ensure that Database is selected for the Authentication drop-down. Then enter progeny as the User ID and enter the progeny user password in the Password field. Only the Progeny super user will be able to login to the database in SQL Central.
3. Ensure that Connect to a running database on this computer is selected for the Action drop-down.
4. Enter the service name of the database that you would like to connect to into the Server name field. This is the -n name that is set up with the database service.
5. Once connected successfully, on the menu bar, click File, then Open Interactive SQL.
6. In the new Interactive SQL window, type BACKUP DATABASE DIRECTORY then type the path of where you want to save the database and log files backup in single quotes. The final statement should look similar to the following: BACKUP DATABASE DIRECTORY ‘C:\Database\Backups’. The database backup files will then be stored in that directory.

Backups from the Progeny Desktop Client

The first step in setting up a backup system for your network databases is to create the appropriate folder structure. You will need to create a backup folder for each database, and inside each folder, you will need a sub-folder for each backup event.

Once your folder structure is in place, launch the Progeny Desktop Client either from your desktop or from the server and log into the network database using the ‘progeny’ superuser. Right-click the folder tree root (look for the little monitor icon) and select Automated Backups…

The Automated Backups menu manages all backup events for the network database. To create a new event, click New and you will be prompted to create a name for your new backup event. This name should correspond to one of the backup event folders you created for this database on the server.

Once you have a name in place, you will choose the backup type and enter the folder path for the backup. In the Backup Details option select Backup Database + Log File unless you already have a different backup program backing up the database and log files, then you may select Backup Log File Only as a second log file backup. When naming your backup folders, you should choose a name that starts with a letter and does not contain any spaces.

 

Type the exact folder path, relative to the server, in the To path: field (local path or network share UNC). Next, you will choose the backup frequency. There are several options here, so we’ll cover them individually.

• Every Day: Backup events will run seven (7) days a week.
• Only On: Backup event(s) will only run on the days specified (checked).
• Once At: the Backup event will run once per day at the chosen time, either every day or on the specific days selected in the first option.
• Every: the Backup event will run repeatedly during each day at the number of minutes/hours specified either, either every day or on the specific days selected in the first option.

Finally, if you choose to have the database run more than once per day (using the Every option), you can set limiting hours on the backup event using the Backup Time (Optional) section. The backup event will begin running at the first specified hour and will stop running at the second specified hour.

When you have completed your configuration of the backup event, click the OK button to commit the event. You should immediately re-open the Automated Backups menu to verify that the backup event was successfully created. If the event shows in the Event Name field (like below) then you know the backup event was successfully created.

Network Backups from the Progeny Server Configuration Utility (Advanced)

The ‘progeny’ super-user can create database backup events directly from within the Progeny Server Configuration Utility by logging directly into the database backend. Open the Progeny 10 Server Configuration Utility application and choose Connections → Connect with SQL Anywhere 17… from the top menu.

Once in the Connect menu, you can enter the connection information using the information fields. When asked for a User ID and Password you will need to use the ‘progeny’ super-user ID and password. In the Actions field, select Connect to a running database on this computer if your Database Service is already running. For the Server Name field, you will need to enter the name that was given to your Database Service. Click the Connect button to connect to the database backend.

Once you have successfully connected to the database backend, you will be shown a screen that contains all the administrative-level functions and options within the database. Using the Folders pane on the left-hand side scroll to the bottom of the list and select Maintenance Plans from the list to view any active Maintenance Plans (Backup Events). If you have previously configured a backup event from within the Progeny Desktop Client, you will see it listed on the right-hand side; otherwise, the area on the right will be empty.

On the right-hand side, right-click the empty space and choose New → Maintenance Plan…

Enter a name for your Maintenance Plan (backup event). Unless your database file is very large (>2GB), there is no need to choose either of the two options shown below the Maintenance Plan name prompt.

On the next screen, you will choose the start date and initial running time for the Maintenance Plan (backup event).

• If this Maintenance Plan will be running only once per day, the time you set here is the time when the daily backup will run. Click Next.
• If this Maintenance plan will run on a repeating (sequential) basis, choose the option at the bottom to specify how often the backup will occur. The Maintenance Plan will first run at the time specified then continue according to the scheduled timeframe you set. Click Next.

Next, you will choose which days of the week the Maintenance plan will run. Here you can select specific days of the week, have it run seven (7) days a week (by choosing all days Sunday-Saturday) or select specific days of the month for it to run. Click Next.

On the following screen, you have the choice to include a database validation when running the Maintenance Plan. This is entirely optional, but Progeny does not recommend that you bother with this screen. Running validations with your Maintenance Plan will not gain you much in terms of database safety and will make the Backup Events take significantly more time to complete. Click Next.

After the Database Validation screen, you will choose the type of backup and the backup location. Here you can choose to back up the database to the local disk or directly to a tape drive (if you have one). When choosing the type of disk backup, you should always choose Full image backup unless you specifically need another type. The Full image backup is the complete backup option. Next, you will choose a location for the backup file. Remember, this file path is relative to the server, not the desktop. Click Next.

Next, you will be prompted with the option of adding custom SQL statements that will run at the start/end of the maintenance plan. Click Next.

Once you click Next you will be asked to specify what you would like to do with maintenance plan reports. Here you have the additional options for saving the last x amounts of reports or to report the current status of the maintenance plan to the server console. Click Next.

On the last screen, you will have the option to have Maintenance Plan reports sent to your email address automatically for each event. This is entirely optional and can be difficult depending on your network and security configurations so use this only if necessary.

This is the final screen of the Maintenance Plan configuration, so click Finish when complete. When the Create Maintenance Plan Wizard closes, you will see your new backup event listed on the right-hand side of the window.

Restoring a Progeny Database from a Network Backup

Should your server crash or the database become corrupted or otherwise unusable, the first step is to stop the Database Service in question. Open the Progeny Server Configuration Utility, click SQL Anywhere 17 → Services Tab, then right-click the database service in question and choose Stop.

Once stopped, you will see the icon next to the database service showing a red square.

Go to the folder containing your active database and either delete the database & log files or move them to another folder for later analysis.

Navigate to the backup event folder with the backed up database you want to restore, and select both the database file and the log file. Right click and copy the selected files.

Paste the copied files into the active database folder.

Now you can restart the network database service by right-clicking the database service and selecting Start. The red square will turn into a green arrow once the database service starts.