Manage Databases

This feature should be utilized for Archiving and Purging data purposes only.

Data Archiving is the process of identifying and moving inactive data out of current production (active) databases and into specialized archival storage area/databases. Restoration is the process of bringing the archived data back into the production system. eCASE provides the following features to support the archiving capability:

• Allow users to automate archiving of data from active (production) system to archive system.

• Allow users to define rules for data archiving (when to archive – time/data specific).

• Allow users to access data from archive system and restore it into the active system.

• Purge data and documents automatically from the active system using purge rules.

• Allow authorized users to change purge business rules and log those changes for review.

• Provide the ability to reload information that was purged in error.

Configure Archive and Purge Database Info in eCASE

To configure Archive and Purge database, you are required to create new eCASE databases that match the current version of your production database. Following are the steps to create a new eCASE database.

1. In the install files, right-click Setup and run as Administrator.

2. In the setup, go to Configuration: Create eCASE Database and click SQL Server.

3. Complete the eCASE v10.6 database setup.

4. Configure the applications and case types in the eCASE.

5. Once done, go to the database server and open Microsoft SQL Server 2012, log in as the Administrator and Connect.

6. On the left module, expand the databases.

7. Right-click the desired database Tasks > Back Up.

8. A window opens. Here, make sure the information is correct and click OK.

9. Once the database has been backed up, right click Databases and select Restore Database…

10. A window opens. Here, select Device and click the browse button “…”

11. Click Add.

12. And select the database that was backed up.

13. The window refreshes.

14. Then, create a new name under Destination > Database:

15. Now go to Files on the left side of the window.

16. For both of the Logical files, edit the Restore As name Database to a different name.

17. Click OK.

18. Database has been restored successfully.

19. Once the Archive or Purge databases are created, go to the eCASE application, Settings > System Configuration > Manage Databases.

20. Click New.

21. Configure new Archive and Purge databases.

22. Once the databases are created and configured, they can be activated or deactivated by selecting a database and clicking Edit. In the edit screen, the database can be activated or deactivated through the Active checkbox.

Inactive databases will not appear for selection when creating archive/purge/restore jobs in Settings > Archive/Purge Data.

Execute Script on Purge Database

IF NOT EXISTS(SELECT * FROM SYS.objects WHERE name='EC_PURGED_FOLDERS')

CREATE TABLE EC_PURGED_FOLDERS(

    [FOLDER_ID] [int] NOT NULL,

    [JOB_ID] [int] NOT NULL,

    [PURGE_DATE] DATETIME NOT NULL DEFAULT(GETDATE()),

    [DELETE_AFTER] INT NOT NULL,

CONSTRAINT [PK_EC_PURGED_FOLDERS] PRIMARY KEY CLUSTERED ([FOLDER_ID] ASC),

CONSTRAINT [FK_EC_PURGED_FOLDERS_EC_BASE_TYPE] FOREIGN KEY([FOLDER_ID]) REFERENCES EC_BASE_TYPE(ID))

GO

IF NOT EXISTS(SELECT * FROM SYS.columns WHERE name='DELETE_DATE' and [OBJECT_ID]=OBJECT_ID('EC_PURGED_FOLDERS'))

ALTER TABLE EC_PURGED_FOLDERS

ADD DELETE_DATE AS DATEADD(DAY,[DELETE_AFTER],[PURGE_DATE]) PERSISTED NOT NULL

Create Search Criteria

1. Folders to be archived: Select Default.

2. Folders to be Restored/Purged: Select Archive Database

3. Folders to be Un-Purged (to be Moved to any Archive DB):

4. Please proceed to System Configuration > Archive/Purge Data.

Important Notes

1. After creating the Archive and Purge databases, any changes to tables in the production database must be applied manually in the respective Archive and Purge databases.

2. If there are any modifications in the application and/or case types, please take a backup of the main database and restore it to the Archive and Purge databases.

3. If there are any field level changes made in the case type/app type/core table level/procedure changes are made those things will not be reflected in the archive/purge database. Those things must be done manually in the respective archive/purge database.

4. When changing the field level of the applications, case types, database, and/or procedures, please make sure that the respective archive and purge databases are changed as well.

5. It is important to maintain the structure of database tables in Archive and Purge tables same as eCASE production database. Also, all the application configuration changes should be applied to Purge/Archive database.