Automate Backups?

Questions and Answers about the POS Pizza 6 and newer Database
Post Reply
pdpfoods
Member
Member
Posts: 12
Joined: Fri Aug 27, 2021 12:19 am

Automate Backups?

Post by pdpfoods »

I am currently running some backup software that will upload some files to a cloud vault for safe keeping. Pretty much like Carbonite. In order for me to backup the PZ6 file every day, say at 5AM, i need to have the DB stopped so the file isn't in use and then schedule the backup for the file, then restart the DB so it is running. I know a small little bit about the Task Scheduler in Windows and was able to at least figure out a way to stop the database at a certain time, but everything else is giving me issues. So, I was wondering that I cannot be the only person that faces this dilemma in here or maybe everyone is doing manual backups every day which seems very daunting. I would love to automate this simple process and was hoping maybe you had already created a simple tool for this. The side note is that many of these backup software companies DO offer this option but as in the case of companies like Carbonite, it goes from 50 bucks a year to over 800 bucks a year so you can automate a capture on a working DB file. That's just beyond insane and not an option here. Any thoughts and/or solutions would be appreciated.
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Automate Backups?

Post by Scott »

The clients determine if the DB is open or closed. Generally the file should be closed if the clients have been inactive for 30 minutes (default) or stopped. Caller ID, Future Orders, and Make-Lines keep the database open on their own. So those would need to be stopped for the file to close on it's own. Depending on the setup this may or may not be easier using something like task manager. The problem with trying to stop the server, is that it is a service and elevated privileges are required.

Maybe in the future we could set a schedule via INI file in the server folder that would tell the server to close all files during a block of time. Another possibility would be to have the server close the files, then copy the files to another destination, then resume on it's own. This could be useful for both local backups or online backups. Online would need to backup the destination drive/folder rather than the active one.

I would like to hear others thoughts on this, because it has been something that is being considered. I know that others are already using Carbonite, etc for backups, but I am guessing that they have their main stations powered off and the db file isn't open when their backup takes place.


Scott
pdpfoods
Member
Member
Posts: 12
Joined: Fri Aug 27, 2021 12:19 am

Re: Automate Backups?

Post by pdpfoods »

So, I am guessing by the looks of this, this can be accomplished by running a task in the Windows Task Scheduler to go like this:

Name of task: POS_DB_Stop *Stop running the database at a determined time such as 4:50am.

Carbonite schedule is set to do a backup of the "Server" folder at 5AM

Name of Task: POS_DB_Start *Restart the database at a determined time such as 5:20AM

The above seems to be a simple temporary work around scenario that could function fine for the time being but I am having a problem with the code to relaunch the database and make it in a running condition. In the first task I tried, I was able to successfully stop the database by using this in the task command line: taskkill /f /im "POSPizzaDBServer.exe"

I just cannot get a task to start the program and then put the DB back in a "Running" Condition.


In and end all game perfect world, it would be best to have a scheduler option for PizzaPOS specifically that would make a copy of the running pz6 file and save it to a specific folder with a common filename that changes constantly with every backup. For instance, automate to make a copy of the pz6 file with the file name PizzaPOS9272021.pz6, (PizzaPOS*******.pz6), So the area with the "*" populates with something unique every day like the date. The example I made was just a random filename that makes sense with todays date in it.
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Automate Backups?

Post by Scott »

pdpfoods wrote:"POSPizzaDBServer.exe"

I just cannot get a task to start the program and then put the DB back in a "Running" Condition.
That is because the server is a service app, not just a regular EXE that can be launched from a command line, or explorer. You would need to target the client apps rather than the server. Stop the following programs (if they are active): All Make-Line screen modules, and the print-only module, Caller-ID server app, and the Future Order server app. The POS, Driver, Manager apps should disconnect after 30 minutes of inactivity (unless you've changed the default), but it would be best to close out of those to the Windows desktop. Maybe even shutdown all of the PCs except the one that is the server.

As long as nothing is connected to the server, then the database file won't be open. Again, the client apps decide if (and which) database files are opened by the server. So if you close all of the clients, then the DB file(s) should be closed and be able to be backed up.

Don't backup the whole folder. You only need to backup the file "POSPizza.PZ6". That is the entire database. If Carbonite can't target specific files, then set a task to copy that file somewhere else, and back it up from that folder instead. All of the other files in that folder come from the server installer, and can be restored by running the server installer which can be downloaded from the forums here.


Scott
pdpfoods
Member
Member
Posts: 12
Joined: Fri Aug 27, 2021 12:19 am

Re: Automate Backups?

Post by pdpfoods »

After we spoke on the phone today, I came home and came up with this decent walking solution that maybe you can expand upon or it helps out another user in here. This should always make a copy of a file that has a unique filename comprised of the filename that had the date and time code inside the name. I have tried this several times and have not had the database keep me from making an automated copy that is timestamped in the filename. Here is the code as follows that I used inside a notepad file that was saved as a .bat file extension so it can be run through the Windows Task Scheduler. I named the notepad file "Task_DB_Bkp_Scheduler.bat" and just saved it inside the root "POSPizza6" file folder.

@echo off
@SETLOCAL ENABLEDELAYEDEXPANSION

FOR /f "tokens=1-8 delims=:./ " %%G IN ("%date%_%time%") DO (
SET datetime=%%G%%H%%I_%%J_%%K
)

copy C:\POSPizza6\ServerDEMO\POSPizza.pz6 "C:\Test Folder\POSPizza_!datetime!.pz6"


I created the directory first that was going to be my save location "Test Folder" at "C:\Test Folder".
I also pointed to the location of the DEMO server folder for the working backup file so I could test the function without playing around with the REAL database file for my POS in case something went wonky.

I automated the service through Windows Task Scheduler by pointing to the "Task_DB_Bkp_Scheduler.bat" I created and set it for a time just a couple of minutes ahead so I could watch it work.

A couple minutes later it successfully saved the file with a unique name that has the date and time inside of the filename so my backup is unique and cannot be overwritten.

I then made a manual backup of the actual working .pz6 file from my functioning POS system as a failsafe and then ran a scheduler again to test a real backup of my actual working DB file. A couple minutes later, the scheduler triggered the .bat file and it made a successful copy with a unique time stamp in the file name. Now my Carbonite backup will backup everything inside of my save folder I created on its backup schedule.
pdpfoods
Member
Member
Posts: 12
Joined: Fri Aug 27, 2021 12:19 am

Re: Automate Backups?

Post by pdpfoods »

Here is an alternate code that can be used that may work better. This code takes care of the 0 problem in the 24 hour timestamp if the copy is made after midnight and before 10am. It also has seconds which makes things perfectly unique.

@echo off
@SETLOCAL ENABLEDELAYEDEXPANSION

SET HOUR=%time:~0,2%
SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2%
SET dtStamp24=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%)

copy C:\POSPizza6\ServerDEMO\POSPizza.pz6 "C:\Test Folder\POSPizza_!dtStamp!.pz6"
pdpfoods
Member
Member
Posts: 12
Joined: Fri Aug 27, 2021 12:19 am

Re: Automate Backups?

Post by pdpfoods »

As a alternative to Carbonite, Venue, etc..., the Microsoft OneDrive also works well by using the copy command in the previous posted code by using this format:

copy C:\*The root location of the database file to backup goes here*\POSPizza.pz6 "C:\*Location of a folder on your OneDrive to save to*\POSPizza_!dtStamp!.pz6"
User avatar
wcpc
Active Member
Active Member
Posts: 42
Joined: Thu Apr 28, 2016 1:49 pm
Location: Charleston, IL
Contact:

Re: Automate Backups?

Post by wcpc »

I do something very similar... I have a ".bat" file on the server that I schedule with Windows Scheduler to make a copy of the .pz6 DB file every morning at 2am... (I also have the .bat file in the Windows Startup Folder so it will run every time the machine is rebooted)

Since these copies are made during "quiet times" I have never had any problem with file locks or inability to copy the file... and I have tested the "backup" file several times to make sure it does indeed work to restore correctly.

I have all my backup files in a directory named "POS_DB_Backups" and I have that directory set as my "shared/synchronized folder" with Google Drive.

The script copies the file, then renames it as a unique file name with a date/time stamp... then it is automatically backed up to my Google Drive.

The backed up files are named, as an example: "POSPizza-20220131- 211.pz6" (where 20220131 is the date and 211 is "two eleven am")

Here is my script:

@echo off
COPY /Y "E:\POSPizza6\Server\POSPizza.pz6" "E:\POS_DB_Backups\POSPizza.pz6"
ren "E:\POS_DB_Backups\POSPizza.pz6" "POSPizza-%date:~10,4%%date:~4,2%%date:~7,2%-%time:~0,2%%time:~3,2%.pz6"

Copy/paste these 3 lines of code into Windows Notepad... modifying the Drive letter etc to match your actual system, then save it as "POSPizzaBkup.bat" (or whatever name you choose)
JimD
Windy City Pizza
Charleston, IL
Post Reply