View source for EEPOS:System Database Recovery with No Backup

== System Database Recovery Methods ==

The below instructions show how to recover the EEPOS System databases from a backup and how to rebuild the system databases if no backup exists.


== Review SQL and Event Logs ==
Before commencing, the SQL error log and Event Viewer logs should be reviewed as these may indicate the original cause of the database problems. 
Any problems indicated in these logs should be resolved before proceeding with the below steps.

'''C:\Program Files\Microsoft SQL Server\MSSQL11.MAGENTAPOS\MSSQL\Log'''

'''Control Panel → Administrative Tools → Event Viewer'''

== Restore With System Database Backups ==

Ensure EEPOS is closed before performing the below steps.

1.	Browse to '''C:\Program Files (x86)\Magenta Retail\EEPOS\Support\SystemDBFiles'''

'''Note:''' If the machine is using 32bit Windows, browse to - C:\Program Files (x86)\Magenta Retail\EEPOS\Support\SystemDBFiles


2.	Check that the system database backups exist.

These files are named with a date stamp and the database name at the end (eg: 20170125.EEPOS, 20170125.master, 20170125.model and 20170125.msdb)

'''Note:''' If the above files do not exist, please follow the Restore Without System Database Backups instructions further below.


3.	Once the backups are confirmed to exist, open '''Command Prompt''' as '''Administrator''' (Right click command prompt → Run as administrator)

4.	Execute the below command to change the directory

<pre>CD C:\Program Files (x86)\Magenta Retail\EEPOS\</pre>

'''Note:''' If the machine is using 32bit Windows, use the below command.

<pre>CD C:\Program Files\Magenta Retail\EEPOS\</pre>


5.	Execute the below command to Recover the System Database Backups.

<pre>SysDbRecovery.cmd RECOVER</pre>


6.	The SystemDbRecovery should detect the system backup files.
Press any key to begin the restore.


[[File:SystemDBRecovery.jpg|SystemDBRecovery]]




This process may take over 10 minutes to recover the system databases. Once completed it should display as per the below screenshot.
Once complete please perform the tests shown at the end of this document.


[[File:SystemDBRecoveryComplete.jpg|SystemDBRecoveryComplete]]




== Restore Without System Database Backups ==

The below instructions show how to recreate the system databases when there is no existing backups.



=== Backup Database Files ===

Before proceeding with the below instructions a backup of the SQL Database files should be performed.

1.	Browse to '''C:\Program Files\Microsoft SQL Server\MSSQL11.MAGENTAPOS\MSSQL\DATA'''

2.	Copy of '''ALL''' files in this folder to a backup folder.

'''Note:''' If a prompt appears saying that the files are currently in use you will need to temporarily stop the SQL Service using the below steps

3.	Open the Windows Services screen (Control Panel →  Administrative Tools → Services)

4.	Stop the SQL Server (Magentapos) service.

5.	Attempt to copy the files again.

6.	Start the SQL Server (Magentapos) service again.


----


=== Stop EEPOS Service ===

EEPOS and the POS Service need to be closed before performing the steps to rebuild the system database.

1.	Close EEPOS.

2.	Open the Windows Services screen '''(Control Panel →  Administrative Tools → Services)'''

3.	Stop the '''EEPOS Service – MAGENTAPOS''' service.


----


=== Rebuild System Databases ===

1.	Open '''Command Prompt''' as '''Administrator'''.

2.	Change the Command Prompt directory to the SQL Installation folder using the below command.

<pre>CD C:\Program Files\Microsoft Sql Server\110\Setup Bootstrap\SqlServer2012\</pre>

3.	Execute the below command to re-build the system databases

<pre>
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MAGENTAPOS /SQLSYSADMINACCOUNTS=Administrator /SAPWD=tempSAPwd /SQLCOLLATION=Latin1_General_CP1_CI_AS</pre>

'''IMPORTANT:''' 

*  SQLCOLLATION needs to match the collation of EEE and other tills, e.g. Latin1_General_CP1_CI_AS.
You can find the collation by opening SQL Management Studio on your EEE Server, then right click the server and select Properties
If you are unsure what collation you should use, please contact Magenta Support for assistance.

'''NOTES:'''

*   Sys Admin account needs to be an account currently logged in so that access to the EEPOS database can be achieved.

*   Ensure SA password is provided. This SA user will need to be used later to connect to the instance.

This process can take some time to complete, there is no indication of progress and it will go back to command prompt when done.


----


=== MSDB and Model databases ===
Copy back any database files (e.g. MSDBdata.mdf, tempdb.mdf, modellog.ldf etc) that you did not need rebuilt (from your backups). You may need to turn off the “SQL Server (MAGENTAPOS)” service to allow access to the files again. Please ensure the service is restarted after this is completed.

The system database files are located in the below folder.
'''C:\Program Files\Microsoft SQL Server\MSSQL11.MAGENTAPOS\MSSQL\DATA'''


----


=== Re-attach EEPOS Database ===

Due to EEPOS using a FileStream for image resources, FileStream access needs to be set against the database before re-attaching the EEPOS database.
1.	Open a new '''Command Prompt''' window as '''Administrator.'''

2.	Run the below command to connect to the Magenta SQL Instance

<pre>SQLCMD –S .\MAGENTAPOS –U SA</pre>

3.	When prompted for a password enter '''tempSAPwd''' as the password. Please note that the password is case sensitive.

4.	Execute the below commands
'''Note:''' You will need to run each command separately in Command Prompt

<pre>EXEC sp_configure filestream_access_level, 1
GO
RECONFIGURE
GO</pre>


[[File:FileStreamAccess.jpg|FileStreamAccess]]



5. The EEPOS Database can now be re-attached using the below command.

<pre>EXEC sp_attach_db @dbname='EEPOS', @filename1=’C:\Program Files\Microsoft SQL Server\MSSQL11.MAGENTAPOS\MSSQL\DATA\EEPOSdat.mdf', @filename2=’C:\Program Files\Microsoft SQL Server\MSSQL11.MAGENTAPOS\MSSQL\DATA\EEPOSlog.ldf'
GO</pre>


[[File:ReAttachDB.jpg|ReAttachDB]]



----



=== Re-create Logins, Linked Servers and Synonyms ===

Please contact Magenta Support to re-create the SQL users, linked server connections and Synonyms.


----


=== Testing ===

Start the '''EEPOS Service – MAGENTAPOS''' service and review the log to ensure it starts up correctly and there are no errors.

The Service log can be found under - '''C:\ProgramData\MagentaPOS\c-program files (x86)-magenta retail-eepos\PosServiceLog.txt'''

1.	Open EEPOS and ensure there are no errors on start up, then test the following:

2.	Create a transaction, ensure the transaction is replicated to the EEE Server. 

In EEE browse to '''Analysis → Queries → Journal''' to check that the transaction appears.


3.	Perform return of the same transaction, this tests communication with EEE, broker queue in EEPOS database and replication of HOS records to EEE.

4.	Review journals (either Journal search from the main menu or from Reprint, Control + R), this tests stored server linked server connection.

5.	Search for a customer. This can be done by pressing F12 to bring up the customer search screen.