Listing files associated with a case from a DES postgresql database dump given a case number

Rarely, you may find yourself needing to enumerate all files associated with a case when DES is offline for the purposes of file recovery or for audit. If you have been given the case number by the customer, you may use the following process to retrieve that information from a postgresql database backup.

The following query assumes that you are either working in the DES server’s postgresql instance, or have loaded a database dump from /fb/xml to another postgresql server using pg_restore.

select med_seq,med_file_path,med_media_fname from fb.media
inner join fb.media_case_folder on mcf_med_seq = med_seq
inner join fb.case_folder on cas_seq = mcf_cas_seq where cas_number=’case number’;

Substitute case number with the actual search term the customer uses to locate their cases in DES