Copying and Backing Up Databases
TSB1015 | Friday, 12 October 2001
This support bulletin has been written from years of experience of users with corrupt data backups - DO NOT IGNORE IT.
finPOWER, emPOWER and fdPOWER can use either a Microsoft Access or Microsoft SQL Server database. This article applies to Microsoft Access (Jet) databases.
To correctly backup or to simply copy a file then that file must not be currently open by any user, ie the computer that is copying the file must have exclusive access to the file.
If a file is written to whilst it is being copied the resulting copy may be corrupt. Consider this scenario:
A database file is half copied (eg 20Mb of 40Mb copied) when a user commits a large batch of transactions to disk. This may amend records at the start of the database as well as in the middle of the database and also add new records at the end of the database. It may update not only data records but also index records. After the commit is complete the copy finishes. In effect records amended at the start of the database have not have been copied whereas records at the end have been. You can see that in this scenario the database will be in a corrupt and potentially un-repairable situation.
The larger the file (ie longer it takes to copy) and the greater the number of users potentially using the file increase the risks involved.
We have discovered that copying an Access database file from Windows Explorer will copy the file regardless of whether it is open or not.
Access database files are open for read/ write within your application. Therefore all users must exit finPOWER, emPOWER or fdPOWER before the file is backed up or copied. Otherwise there is nothing stopping a user from updating information in the database at any stage.
Note that whenever a window within POWER is closed the window's position and size is saved to the database, so even if you think a user is just sitting in finPOWER not writing any information to disk there is still a potential risk.
This is the recommended way to copy a database. It will fail if the database is open.
As already mentioned the Windows Explorer will copy open files. We do not recommend its use to copy an Access database.
There are a multitude of Backup Utilities available, so we will not attempt to cover them here. Instead we strongly recommend you test the backup utility.
- Open finPOWER, emPOWER or fdPOWER and make certain your database is open.
- Open the utility and attempt to backup your database.
- If the programs fails with an error relating to the database file being open then it passes the test.
If your backup utility fails the "Error attempting to backup an open file" test then there is no guarantee your backups are working correctly. We strongly suggest using another backup utility.
WARNING: Never ignore a warning or error message.
Note, some backup utilities include a "Verify" option. This means that after the backup job has finished the backup is re-run again, this time the contents of the freshly created backup file are compared to the original files. If they do not match an error is generated.
The verify option is an added level of protection.
Some users choose to run an unattended backup in the middle of the night. This works well, but remember to check the backup log for errors the next morning.
A backup is only of any use if it can be guaranteed to be a 100% faithfully representation of the original. It is vitally important, therefore, that you make sure this is the case - be proactive in doing so and make sure you never have a backup nightmare on your hands.