From EnablerWiki

Created: 23 January 2009. Last Reviewed: 23 January 2009

Restoring EEE

While this document outlines the minimum data backup requirements and explains the processes involved in recovering EEE, it is by no means a step by step procedure on how an individual client should recover their EEE database(s).

The following information has been tested in a controlled environment, and is likely to be different to your production environment. For a full disaster recovery process for your environment, you should use the information here to test in your own controlled test environment.

Contents 1 What should be backed up 2 Restore of EEE to machine of the same name 3 Restore of EEE to machine with different name 3.1 Recovering Security settings 3.2 Recovering EEEExternalUsers settings 3.2.1 SQL Server Authentication 3.2.2 Windows Authentication 3.3 Setting up GL Export security 3.4 Verifying the restored database 4 Recovering SQL jobs (Restore MSDB) 5 Restore of EEE including transaction logs

What should be backed up

EEE includes a number of SQL backup jobs that perform backups of SQL databases to backup devices in a specified location. These backup devices will have names such as MWFSDB.BAK and MWFSLog.BAK. While these backups will allow you to restore your EEE database in the event of corruption or other problems with your EEE database, they will not help in the event of disk failure which makes your EEE database, and the database backups unavailable.

For this reason, the EEE backups should not be your sole backup of your EEE database. The backup devices mentioned above should then be backed up to another device such as a tape backup or external backup device.

The EEE backups are really only a starting point for backups of EEE. Each client should verify that the standard backups provide the level of security they require. If you implement your own backup procedures, such as tape backup using an SQL Agent, you should disable the standard EEE backup jobs.

In addition to the backup of your SQL databases, there are various other configuration files used by EEE that should be backed up also. These include:-

1. The eeedata import,export and especially the archive folders.
2. Any file with a .bat or .cmd extension in the subfolders of EEE. An example of these would be in the export folder where most clients have an export batch file modified to their environment.
3. Any file with a .txt extension in the subfolders of EEE. These could include a modified filelist.txt from the export folder, which can be used to modify the files exported to stores.
4. All files in the c:\inetpub\wwwroot\EEE\CompanyConfig folder. This folder will contain various files including files used for Purchase Order printing, and Debtor statement generation.
5. If you have modified EEE menus so that users only see menus available to their user class, there will be a file called menus_by_class.xml located in c:\inetpub\wwwroot\EEE that should also be backed up.
6. If you have batch files or software to transfer data to/from other systems, these should also be backed up.

Ensure you also document the SQL collation of your server, and also all the Windows regional configurations for your server as this impacts on the SQL collation.

Restore of EEE to machine of the same name

The following is an overview of the procedures involved in restoring EEE to either the same server (rebuilt after a failure) or to a new server with the same name as the old server.

There are 2 methods that can be used to perform a restore to a machine with the same name. The first method would be the preferred option of restoring master and msdb databases, followed by restoring EEE. The 2^nd option is to only restore EEE and MSDB and use the EEE database setup program to recreate scheduled jobs and SQL logins.

Option 1

This procedure assumes that you have a current backup of MASTER, MSDB and EEE databases.

• Reinstall Windows with the same computer name. Ensure same SP installed.
• Install SQL Server with the same service pack and same collation.
• If using Local accounts, create the Windows groups EEE Users and EEE Admins. These are normally created by the database setup, but that step is being skipped here.
• Restore MASTER database. Follow the procedure below to restore MASTER.

(i) Stop SQL Server and all SQL services(ii) Restart SQL Server in single user mode i.e. sqlservr -c –m -f(iii) Restore Master by running SQLCMD and typing the command RESTORE DATABASE master FROM DISK = 'C:\EEE\BACKUPS\MWFSDB.bak' WITH FILE=2, RECOVERY, REPLACE; GO. When using EEE backup devices (MWFSDB or TTSDB), the FILE=2 option is mandatory as it tells the restore that master is the 2^nd backup on the device. Substitute the correct path in this command.(iv) Restart SQL Server normally. If SQL Agent fails to start, see the section on SQL Agent at the end of this topic.

• Restore MSDB followed by MODEL databases. These can be restored from the command line or from SQL Server Management Studio.
• Restore EEE database. If you have transaction logs to restore also, use the RESTORE WITH NO RECOVERY option.
• If applicable, restore EEE transaction logs.
• Run the TSQL command: “alter authorization on database::eee to SA” against the EEE database. This is required for later re-install of magenta_sql.dll.
• Install and configure all EEE components (except database setup!) . Don’t start Data Loader or Query Server.
• If you need to re-process HOS files since the last transaction log that was restored, you will need a backup of the EEE archive folder. Restore the relevant files to the EEE Import folder. It doesn’t matter if you restore files that have already been processed in the restored EEE database, as EEE data loader will ignore these transactions.
• From a browser go to SQL Server Reporting Services (normally http://localhost/reports) and make EEEServices account a Content Manager.
• From IIS Manager, change the ReportServer folder to use Anonymous access with the EEEServices account.
• Verify in IIS that the EEE folder is using ASP.NET v2.0.50727. If this is 1.1.4322, you won’t be able to login to EEE and the step of applying the service release will fail.
• Verify that you can get to the EEE login screen, and then use the ADD Company button to setup your company again.
• Create a folder called SQLDLL in the EEE folder (say c:\eee\sqldll) and copy the file magenta_sql.dll into this folder. This DLL file can be found in the sqldll folder for the latest service release.
• Run the query: sp_changedbowner 'sa'
• Run the query exec usp_CLRUpdate 'eee', 'C:\eee\SQLDLL\Magenta_SQL.DLL' adjusting the database name and path name where applicable.
• Apply the same EEE service release that was applied on the old machine.
• Verify that EEE components such as EEE Data Loader and EEE Exporter are working correctly.
• The EEE Exporter folder will usually contain a batch file for running EEE Exporter. You may need to restore this file if recovering an entire server.
• The c:\inetpub\wwwroot\EEE\CompanyConfig contains 3 files that you may need to restore or re-create. (A fourth CompanyConfig.xml also exists in this folder but that is created/updated when new companies are added within EEE.)EEEStatementsCustom.xml – Used by Debtor Statement GenerationPurchaseOrderCompanyAddress.txt – Used to print the company address on Purchase Orders.PurchaseOrderFirstPageNotes.txt – Used for recording notes that will be printed on the 1^st page of a Purchase Order.

SQL Agent fails to start

If you attempt to start SQL Agent after restoring the master database, and it fails, and the Windows log has the error: SQLServer Agent could not be started (reason: Error creating a new session), follow the procedure below.

1. From SQL Management Studio, go to Security->Logins and select the account used to start SQL Agent.
2. Right click on this account and go to Properties
3. Click on Server Roles, and tick the role of SYSADMIN
4. Start SQL Agent.

Option 2

This procedure assumes that you have a current backup of EEE and MSDB databases.

• Reinstall Windows with the same computer name. Ensure same SP installed.
• Install SQL Server with the same service pack and same collation.
• Install and configure all EEE components and apply the same service release that was applied on the old machine.
• Stop the EEE Data Loader and EEE Query Service services (applying the service release automatically starts these services).
• Restore EEE database from backup. As the database is being restored to the same server name, there are no issues to be addressed with user securities.
• If you have added your own SQL jobs or changed the standard EEE jobs, you maybe able to restore these by restoring the MSDB database. Refer to the section at the end of this document about restoring the MSDB database.
• From a browser go to SQL Server Reporting Services (normally http://localhost/reports) and make EEEServices account a Content Manager.
• From IIS Manager, change the ReportServer folder to use Anonymous access with the EEEServices account.
• Verify that you can login to EEE.
• If you need to re-process HOS files since the last transaction log that was restored, you will need a backup of the EEE archive folder. Restore the relevant files to the EEE Import folder. It doesn’t matter if you restore files that have already been processed in the restored EEE database, as EEE data loader will ignore these transactions.
• Restart EEE Data Loader and EEE Query Service services.
• Verify that EEE components such as EEE Data Loader and EEE Exporter are working correctly.
• From EEE 1.39, the backup device names have changed from MWFSDB to have the database name prefixed in front of the device name, i.e. EEEMWFSDB. If you installed EEE using 1.39, and then restored your backup for a database that was ORIGINALLY setup using an earlier version, you will need to change the backup device names in the EEE Stored procedures MWFSFullBackup, MWFSLogBackup, TTSFullBackup and TTSLogBackup.
• The EEE Exporter folder will usually contain a batch file for running EEE Exporter. You may need to restore this file if recovering an entire server.
• The c:\inetpub\wwwroot\EEE\CompanyConfig contains 3 files that you may need to restore or re-create. (A fourth CompanyConfig.xml also exists in this folder but that is created/updated when new companies are added within EEE.)EEEStatementsCustom.xml – Used by Debtor Statement GenerationPurchaseOrderCompanyAddress.txt – Used to print the company address on Purchase Orders.PurchaseOrderFirstPageNotes.txt – Used for recording notes that will be printed on the 1^st page of a Purchase Order.

Restore of EEE to machine with different name

The following is an overview of the procedures involved in restoring EEE to a new server with a different server name. This assumes that Windows has already been installed on this machine. This procedure assumes that you have a current backup of EEE database.

• Install SQL Server with the same service pack and same collation.
• Install and configure all EEE components and apply the same service release that was applied on the old machine.
• Verify that you can login to EEE.
• Stop the EEE Data Loader and EEE Query Service services.
• Restore EEE database from backup.

Recovering Security settings

The following procedures only apply when using Local Machine accounts.

If you are using Domain Accounts for EEE, then your SQL logins will be correct after the database has been restored.

• From SQL Server Management Studio, go to Databases->EEE->Security->Users and delete EEE Admins, EEE Users and POS Users. These users will have the old machine name in front of them, i.e. OLDSERVER\EEE Admins.
• From Security->Logins all the required users/groups should exist following the reinstallation of EEE. For all the above groups, you will need to go to Properties and then User Mapping for each user/group and map them to the EEE database and then assign the following role memberships.

EEE Admins	EEE Users	POS Users
Db_datareader	Db_datareader	Public
Db_datawriter	Db_datawriter
EEEAdmin	Db_ddladmin
Public	EEEQuery
	EEEUser1
	Public

Recovering EEEExternalUsers settings

There are 2 configurations for Excel to access the EEE database. These are using Windows Authentication or SQL Server Authentication. Depending on which method of authentication you are using will decide which option to follow below.

SQL Server Authentication

• Make sure that SQL Server is running in Mixed Authentication mode.
• From Security->Logins, add a new user called EEEExternalUser. Ensure you select “SQL Server authentication” and remove the tick from “Enforce password policy”.
• Give this new login a password
• Map the login to the EEE database, and assign role membership of EEEExternal and Public.

Windows Authentication

• From SQL Server Management Studio, go to Databases->EEE->Security->Users and delete EEEExternalUsers.
• From Computer Management, create a Local Machine group called EEEExternalUsers. Add required users to this Windows group.
• From SQL Server Management Studio, go to Security->Logins and create a new login for the EEEExternalUsers group
• For the above login, map to the EEE database and then assign the role memberships of EEEExternal and Public.

Setting up GL Export security

• If you are using the General Ledger export functionality, there will also be a user called GLExport in Databases->EEE->Security->Users. If so, delete this user. From Security->Logins, add a new user called GLExport. Ensure you select “SQL Server authentication” and remove the tick from “Enforce password policy”. Enter the password of 46YZWsf# and map the login to the EEE database, and assign role membership of db_datareader, db_datawriter, EEEAdmin, EEEExternal and Public.

Verifying the restored database

• Verify that you can login to EEE.
• In “Company Preferences” in EEE, change the URL for Reporting Services to the new server name.
• Restart EEE Data Loader and EEE Query Service services.
• Verify that EEE components such as EEE Data Loader and EEE Exporter are working correctly.
• From EEE 1.39, the backup device names have changed from MWFSDB to have the database name prefixed in front of the device name, i.e. EEEMWFSDB. If you installed EEE using 1.39, and then restored your backup for a database that was ORIGINALLY setup using an earlier version, you will need to change the backup device names in the EEE Stored procedures MWFSFullBackup, MWFSLogBackup, TTSFullBackup and TTSLogBackup.
• The EEE Exporter folder will usually contain a batch file for running EEE Exporter. You may need to restore this file if recovering an entire server.
• The c:\inetpub\wwwroot\EEE\CompanyConfig contains 3 files that you may need to restore or re-create. (A fourth CompanyConfig.xml also exists in this folder but that is created/updated when new companies are added within EEE.)EEEStatementsCustom.xml – Used by Debtor Statement GenerationPurchaseOrderCompanyAddress.txt – Used to print the company address on Purchase Orders.PurchaseOrderFirstPageNotes.txt – Used for recording notes that will be printed on the 1^st page of a Purchase Order.

Recovering SQL jobs (Restore MSDB)

Follow the procedure below to restore the MSDB database which contains all scheduled SQL jobs.

Note this restore is only possible if the SQL version on the rebuilt machine is identical to the machine that the backup was taken on, e.g. 9.00.3040 or 9.00.3054.

• Stop SQL Server Agent.
• Restore MSDB database from backup.
• After MSDB has been restored, restart SQL Agent from within Management Studio, and then press F5 to refresh the jobs list.

Restore of EEE including transaction logs

This procedure would be used in the event of an event such as serious database corruption, or disk failure resulting in the loss of the disk containing the EEE database. In this scenario, the restoration is unplanned, so you would like to recover the database as close as possible to the failure time. This would involve restoring the database backup followed by transaction log backups.

• Stop the EEE Data Loader and EEE Query Server services.
• Restore EEE database. If you have transaction logs to restore also, use the RESTORE WITH NO RECOVERY option.
• If applicable, restore EEE transaction logs.
• If you need to re-process HOS files since the last transaction log that was restored, you will need a backup of the EEE archive folder. Restore the relevant files to the EEE Import folder. It doesn’t matter if you restore files that have already been processed in the restored EEE database, as EEE data loader will ignore these transactions.
• Restart EEE Data Loader and EEE Query Server services.