Backups, logging and recovery

In case something goes wrong with your computer, network, or software, it is very important that you do not lose your Collections application and all the data in your databases.
It is only a small effort to protect this data, so that when necessary you can retrieve it up to the last record that was added or modified. You can do this through backups and through Logging & recovery.

Requirement 1: regularly back up your data.

A backup is a (sometimes compressed, e.g. by zip) copy of your data. Depending on the frequency with which your data is changed, we advise you to make a backup of your data once a day, once a week or once a month. Always make a backup before you start reindexing or importing, or when you intend to make changes to the application or restart a logging file.

SQL – In case your database is stored on SQL Server, you’ll have to use the relevant server management software to create backups of the database. Changes in your application can be secured by copying your main Axiell application folder to e.g. a USB-stick, an external hard drive, a ZIP-drive or a tape. A database backup could be created as follows:

1. In Microsoft SQL Server Management Studio Express, right-click the database, and in the pop-up menu choose Tasks > Back up.
2. Set the Backup type to Full. This will save the entire database, including transaction log, in a back-up.
3. Set other options (also see the Options tab) as desired.
4. Click OK to generate a backup.

It is also important to make backups of your digital media files if those are not stored in a DAMS (Digital Asset Management System). The Collections database normally only holds a reference to a digital image or document, the media themselves should also be kept safe.

For all backups goes: test the backups that you make from time to time, and save them separately somewhere, away from your original files on offline devices.

Requirement 2: keep an automatic logging file.

On loss of application or database definition files, you will have your application(s) back by copying and pasting a backup of your Axiell software folder.
In the case of a corrupt SQL database, the database must be restored via the relevant server management software: this will automatically bring the backup up-to-date with the aid of the transaction log which has been kept by the server.

SQL logging – In Axiell Collections, the basic operations which comprise write actions, are bundled in so-called transactions. The SQL recovery model makes sure that transactions are saved only if all basic operations of which they consist, have been completed successfully. Microsoft SQL Server supports three different recovery models:

• Simple, the default and most basic recovery model, in which each successful transaction is removed immediately. Therefore, the log remains very small. This won’t allow you to restore a database to an earlier point in time.
• Bulk logged, in which all transactions will be logged, except bulk operations. (In this context, Collections import and export are no bulk operations.)
• Full, in which all transactions will be logged. We recommend using this model.

The recovery model could be set as follows:

1. In Microsoft SQL Server Management Studio Express, right-click the database, and choose Properties in the pop-up menu.
2. Select the Options tab and set the Recovery model to Full.
3. Open the Files tab and set a path to the logging file: behind the logging file in the Database files list, click the ... button (just left of the Path column). Make sure that the logging file (and backups) are not stored on the same disk as the database itself.

SQL Server database restore (repair with .ldf log)

A corrupt SQL Server database can be repaired with the Restore function. By default, the most recent backup of the backup and the active transaction logging file are used to bring the database up-to-date again, but you may restore to an earlier time as well.

Before you can start restoring the database, you’ll have to make a backup of the transaction log, for instance as follows:

1. In Microsoft SQL Server Management Studio Express, right-click the database, and in the pop-up menu choose Tasks > Back up.
2. Set the Backup type to Transaction log. This will only save the transaction log in a backup.
3. On the Options tab, mark the Back up the tail of the log… option. Set other options as desired
4. Click OK to generate the backup.

After that, you could use Restore as follows:

1. In Microsoft SQL Server Management Studio Express, right-click the database you wish to repair, and in the pop-up menu choose Tasks > Restore > Database.
2. In From database, select the backup which should be used, or, in From device, select the logical or physical backup device (for instance a tape drive or files) which contains the backup.
3. In the Select the backup sets to restore list, mark the components of the backup which you want to use for restoring (the recovery plan suggested by default - the already marked components - is probably advisable).
4. On the Options tab you may set other options. The Help function on each tab offers information about all functions.
5. Click OK to execute the restore procedure.

Managing the logging file

SQL Server logging file – The logging file of a SQL Server database keeps on growing if you use the Recovery model: Full (as recommended by Axiell). If the transaction log is getting too big, you’ll have to purge it. The best way of doing that is by creating a Full backup of your SQL database first (via the server management software), then setting the Recovery model to Simple, followed by reducing the transaction log via the Shrink option, after which you reset the Recovery model to Full, for example as follows:

1. In Microsoft SQL Server Management Studio Express, right-click the database, and in the pop-up menu choose Tasks > Back up. Set the Backup type to Full and set other options as desired, to generate a backup.

2. Right-click the database, and in the pop-up menu choose Properties > Options. Set the Recovery model to Simple.

3. Right-click the database, and in the pop-up menu choose Tasks > Shrink > Files. Set the File type to Log. Check the Available free space, select Reorganize pages… as the Shrink action, and set an accompanying file size (e.g. 1 MB) before you click OK to shrink the logging file.

4. Right-click the database, and in the pop-up menu choose Properties > Options. Set the Recovery model to Full.

Securing changes in applications

Modifications in your application configuration files and possible output formats of your own design cannot be registered in a logging file. But every time you make a backup of the entire Axiell software folder, you also backup any changes you have made to the application and associated files yourself. So ensure that you make a backup before you make any changes to the application, and make another backup afterwards. You can use the first backup in case the new application does not work properly, and the second backup if the new application disappears from your system or gets corrupted. You can also retrieve separate screens (.fmt files) from a backup.