Backing up and restoring eMER accounts

When should an eMER account be backed up?

• CAMIS developer (Avro Arrows and Medevac) as well business support accounts being used on acceptance should be backed up before the refresh.

• If you have an account on development and wish to recreate it on acceptance (or vice versa). You will have to modify some values in the scripts that are exported, due to sequences that exist on some tables, as explained in this document.

• other occassions, as needed.

Identifying the accounts to backup:

This script returns the developer accounts, for Team Avro Arrows and Team Medevac, as well as the business that are currently active on the target environment.

select * from YA096_PERSON
where upper(LAST_NAME_NM) in ('ASGILL', 'AMIN', 'COLLINS', 'DERTINGER', 'KHAN', 'MAGHUR', 'NGUYEN', 'WANG', 'SHIRZAD', 'LE', 'BEDARD', 'DAVID-OMBIGA',  'KENNEDY', 'PEREIRA', 'ROY', 'SAMANT', 'ZOBARICH', 'BAPTISTE', 'DAWI' )
and upper(FIRST_NAME_NM) in ('ALAIN', 'HIREN', 'CHRIS', 'NOREEN', 'SAM', 'SUFYAN', 'PHUONG', 'HAOYUAN', 'SIAMAK', 'MICHELLE', 'ALEXANDRE', 'BOJE', 'RYAN', 'FABIO', 'VERONIQUE', 'MOHIT', 'RICHARD', 'DANIEL', 'SAMIA')
and DATE_DELETED_DTE is NULL
--and upper(EMAIL_TXT) like ('%TC.GC.CA')

order by LAST_NAME_NM asc;

Export the records to “sql” script files for the following tables:

These tables contain the data for a fully functional eMER account. The tables are presented in the order that the insert scripts need to be in, in the final script that is constructed to restore the record.

• CAMIS.YA096_PERSON

• CAMIS.YA001_MEDICAL_EXAMINER

• CAMIS.YA006_PERSON_TELEPHONE

• CAMIS.UA031_MEDICAL_EXAMINER_OFFICE

• CAMIS.UA041_MEDICAL_OFFICE_TEL

• CAMIS.UA045_STAMP

• CAMIS.AA008_APPLICATION_USER

Also backup the reference table TC019_ADDRESS as you may wish to restore addresses if a refresh is being done from production to acceptance or development. Production may not have all the same addresses.

Exporting data to “sql” script files:

take note of the PERSON_ID of the account in YA096_PERSON from the script run earlier in this document.

Retrieve the account to be backed up;
For example:

select * from YA096_PERSON 
where PERSON_ID = 762818; -replace the PERSON_ID with the one corresponding to the account.

Right click on the result set.

Select “Export Dataset”.