"Hot" Database Copy Possible?

[dcr_Moes]

I currently have the .PZ6 database backed up in the early morning hours for the pizza restaurant I support. Since the .PZ6 file is locked for exclusive access during normal daily transactions I wonder if it would be possible for the server to create a "copy-able" copy of the database file every few minutes that could then be copied to another PC? What I am looking at is a way to recover from a catastrophic event that takes down the main server during business. I have a semi-hot backup server in place that could be up and running as soon as a "current" .PZ6 file is available for it (and the POS Pizza dongle is moved). Obviously, it would be great to get the very latest .PZ6 file and not lose any transactions for the current day but this may not be easy to do. Has anyone done anything like this?

[Scott]

The server can't do this, because it would need to disconnect everyone, then make a backup. This would cause constant "lags" for the clients.

A backup app that can copy files which are "in-use" would be a better solution. Many backup apps can copy open files, since they copy in "shared" mode rather than exclusive.

Scott

[dcr_Moes]

I have been researching and don't see an obvious solution for the "in-use" copy. There was a HoboCopy utility that is pretty old mentioned in some online sources but I haven't found an executable to download (for Windows 10). There are references to a Windows service (Extended VSS - Volume Shadow Copy Support) that is utilized by this type of copy program to do the "in-use" copying so it may be possible. The only program I found that MIGHT do the in-use copy looks like it is for Windows Servers so I'm not sure if that would work for us. (It's also not cheap.)

We may have to go to using the previous day's database file which is copied off to an external drive in the early morning hours. I do wonder if we encounter an error during business hours and have to switch to the backup server and the previous day's file...what state will the POS terminals be in? Will we have to close out the terminals and start a new day with new cash amounts...or ???

[Scott]

You may want to consider backing up to a NAS. This will have an up-front cost to it, but it's a good way for on-site backups.

Synology has several included backup solutions with theirs, but Active Backup for Business is one that can copy files from any machine, even when in-use. It does backup entire volumes (partitions), so you would need to setup the database server on it's own partition, which wouldn't be too hard to do. You can then create a task in Active Backup to backup every hour. Synology NASs generally start at around $300 and go up depending on the number of drive bays that you want. You could start out with a single drive in a 2-bay unit, to cut costs. This is a one-time investment, so there aren't recurrent costs after the initial purchase. Just avoid some of their really low end units, as they don't have the full feature set.

Scott

[Scott]

I was able to throw together a quick little utility app that copies the database file into the same folder. Moving it elsewhere will need to be done via a batch file or other means. This file can be called beforehand and will make a copy of POSPizza.PZ6 named POSPizzaBU.PZ6 in the same folder.

Unzip the attached file into your main server folder. Call the app with the command line switches -nk -np if you are automating the process.

See the included PDF for more details.

I did some testing here and it works. Note, that in some cases if a station was doing a write to the DB at the same exact moment, this app is running, corrupt data is a possibility. This shouldn't be a major issue if you are running backups at very regular intervals since the likelihood of corruption will only be during extremely heavy-use periods.

Scott

[dcr_Moes]

I just read your posts/replies. I will give your app a try-out in the next couple of days. Thanks!

[dcr_Moes]

OK, your utility seems to be working and will definitely give me the tool I need to setup the "hot" recovery procedure. I do have a question regarding what happens during a hot copy when data corruption (may) occur. Will your utility log an error in that case? I have not seen any error log entries so far after several days of usage. This system is for a mom/pop pizza restaurant with just 2 POS terminals (and usually just one is being used). I'm thinking that the chances for corruption are pretty slim. They can get pretty busy during meal hours but it's not like the database is going to get slammed with multiple simultaneous requests at any point in time. Thanks for providing this tool!

[Scott]

I do have a question regarding what happens during a hot copy when data corruption (may) occur. Will your utility log an error in that case?

The tool will only log errors that it is aware of. If it can successfully read a block of data, and then write that block to the new file, then as far as it is concerned, no errors have occurred.

The errors would be seen from the SQLite side of things, like a record being written, but the index was not updated, or data still sitting in temp buffer, etc. Most of the time SQLite is really good at recovering from these types of errors, but the potential exists that you may have to run an external tool on that particular copy in order to reindex it, and/or repair it.

**I STRONGLY SUGGEST** also installing the latest server update, which includes an updated version of SQLite, that not only greatly enhances performance, but is likely better at recovering from certain errors too.


Scott

[dcr_Moes]

Suggested update to server noted. I will do that this Sunday evening (the restaurant is closed on Mondays).
Thanks.