Making a backup of the DB while users are logged in to it can result in a number of problems, such as locked records that you can't access in the backup or possible inconsistencies if you have transactions occurring.
Copying new records to a copyof the same table that has an autonumber column will also create problems, since the autonumber column might renumber itself.
Copying the full db to another machine when everyone is off is an excellent way to make a backup. If you need to have hourly or more frequent copies, here's a suggestion of where to start. I recognize it will slow the database down, but if you need the frequent backup, it will solve the problem:
1. Create another database will all tables that need to be maintained with current data. (Make any autonumber columns into number columns.) To reduce delays, eliminate all indexes and relationships in the copy.
2. Link the operating db to the tables in the backup database.
3. Add code to your application so that when a record is saved (whether a new record or an update of a record), the new or changed data is also saved in the corresponding backup table. Adding this code will be a pain, but it will give you a minute by minute backup. You might also include a date/time posted column in each of the differential backup tables.
You should then set up a backup procedure where at some point in time you copy the differential backup and keep it with the previous backup, then copy the entire DB (witheveryone off) and delete all records in the current differential backup, so the differential backup will only contain records added or changed since the previous full backup.
Of course, as a final "step" you should write some admin code that willpost the differential db records to the last backup to bring it up to date in case of failure of the current db.
