View source for EEPOS:System Database Recovery

POS has been modified to implement a nightly backup of the EEPOS database, and select system databases (master, msdb, model), performed by the POS Service.<br/>
POS will keep 2 nights worth of backups (located in C:\Program Files (x86)\Magenta Retail\EEPOS\Support\SystemDBFiles), and will remove any additional backups on the completion of the latest backup (ensuring each till will have a minimum of 2 days worth of backups).

This has been implemented to ease recovery in the instance of a corrupted SQL Server installation, which can occur due to I/O issues on the machine (e.g. disk issues, power loss during operations being performed on a database).

The file size of the system database backups will be minimal, however the EEPOS database backup file size is dependant on the content in the database. There is a limitation of 10GB on the file size of the database, which means that the maximum backup file size should be close to this size.

Additionally, we have implemented a once-off copy of the system database files (master.mdf, msdb.mdf, model.mdf, mastlog.ldf, msdblog.ldf, modellog.ldf) to the POS directory during the installer.<br/>
This process utilises a script (SysDbRecovery.cmd) located in the main EEPOS directory (e.g. C:\Program Files (x86)\Magenta Retail\EEPOS) which can be used separate from the installer if required.

This script performs the following:
* BACKUPPARAMS  - Records required parameters for the backup, restore and recovery processes, and outputs the parameters to params.cmd. This is automatically referenced by each of these processed.
* BACKUP        - Performs a file copy of the system database files to C:\Program Files (x86)\Magenta Retail\EEPOS\Support\SystemDBFiles.
* RESTORE       - Performs a copy of the backed up copies of the system databases, and then restores the latest backups of the system databases (created by the POS Service)
* RECOVER       - Regenerates the system databases from scratch, and then restores the latest backups of the system databases (created by the POS Service). If the backups do not exist, only the regenerate of the system databases will be performed

Running the file without any parameters will display the paramaters and their meanings:

<pre>Usage:  SysDbRecovery <command> [instance-name (default=MAGENTAPOS, use . for default instance)]

where <command> is:

BACKUP        - make initial backup copies of system databases
BACKUPPARAMS  - just record required backup parameters
RESTORE       - restore backed up copies of system databases, then restore latest SQL backups
RECOVER       - regenerate system databases, then restore latest SQL backups</pre>

For the instance name parameter noted above, three options are available:
* Parameter not provided - the script will assume the instance name for the SQL Server is MAGENTAPOS (the default instance name installed during a EEPOS installation)
* Parameter provided as "." - the script will run the required operation on a local SQL Server Default Instance.
* Parameter provided as an instance name - the script will run the required operation on the specified SQL Server Named Instance.

Further details of what is performed for each option is noted below:

----

'''BACKUP'''
* Turn off the SQL Server
* Copy the system database MDF and LDF files to C:\Program Files (x86)\Magenta Retail\EEPOS\Support\SystemDBFiles
* Restart SQL Server
* Restart the POS Service (may throw an error if the POS Service is not yet installed, this is an acceptable error)

----

'''BACKUPPARAMS'''
* Create the parameters required for this script to run (storing the parameters in params.cmd)

----

'''RESTORE'''
* Check for the existence of the backup MDF/LDF files
** If any of the files are missing, the script will not continue
* Check for the existence of the backup files created from the POS Service scheduled task
** If any of the files are missing, the script will not continue
* The script will then prompt the user to continue or cancel
* Once the user confirms to continue, the script will
** Turn off the SQL Server
** Copy the system database MDF and LDF files to their SQL Server data path
** Restart SQL Server
** Perform the restore of the databases
** Restart the POS Service (may throw an error if the POS Service is not yet installed, this is an acceptable error)

----

'''RECOVER'''
* Check for the existence of the backup files performed from the POS Service scheduled task
** If the script is unable to find them, it will display a warning noting that the default system databases will be generated, but no backups will be restored
* The script will then prompt the user to continue or cancel
* Once the user confirms to continue, the script will
* Turn off the SQL Server
* Regenerate the system databases
* Restart SQL Server
* Perform the restore of the databases from the backups
* Restart the POS Service (may throw an error if the POS Service is not yet installed, this is an acceptable error)

----