How to create a backup of a cloud application to SQL Server using the APIs through Invantive SQL?

Wiki (en)
,
1

All applications have a lifecycle and at the end, part of the data is typically migrated into it’s successor.

However, large parts of the original data typically can not be loaded into it’s successor or - even when possible - is registered with the wrong effective dates or other semantic changes.

There are two approaches to keep access to the original data, which can be executed separately or combined:

  • extend the subscription,
  • create a backup of the data and documentation in another file format or database.

The extension of a subscription is a great solution, but can have a number of drawbacks:

  • extensive archival periods can incur significant costs, even when the user count is reduced,
  • the supplier can change pricing in the sunset phase or altogether withdraw the product from the market.

Creating a backup often provides an additional mean and is typically possible with a Free Trial or Free Plan on the Invantive Query Tool or other Invantive SQL-products. In this topic it will be shown how to store data in a cloud application like Exact Online, ActiveCampaign, Visma AccountView or JIRA in files or a database.

Copy Cloud Application Data to JSON Files

The following Invantive SQL code exports each table of a cloud application to an independent JSON file for archival purposes:

create or replace procedure backup2json
( p_table_name                    varchar2
, p_provider_data_container_alias varchar2
, p_file_name                     varchar2
)
is
  l_stmt varchar2;
begin
  dbms_output.put_line('Start copy of ' || p_table_name || '.');
  l_stmt := 'insert into write_file@os( file_contents, file_path) select ascii_to_blob(json) , '''
            || p_file_name
            || ''' from'
            || '( select /*+ http_disk_cache(true) http_memory_cache(false) */ * from '
            || p_table_name
            || '@' 
            || p_provider_data_container_alias 
            || ' src for json auto)'
  ;
  execute immediate l_stmt;
  dbms_output.put_line('Copied rows from ' || p_table_name || ' to ' || p_file_name || '.');
exception
  when others
  then
    dbms_output.put_line('Exception on copy ' || p_table_name || ': ' || sqlerrm);
end;

begin
  for r in
  ( select call_safe_name
    ,      provider_data_container_alias
    from   SYSTEMTABLES@DataDictionary
    where  provider_name = 'ActiveCampaign'
    and    schema = 'V3'
    and    type in ('table', 'tablefunction')
    order
    by     call_safe_name
  )
  loop
    backup2json(r.call_safe_name, r.provider_data_container_alias, 'c:\temp\' || lower(call_safe_name) || '.json');
  end loop;
end;

Copy Cloud Application Data to SQL Server

The following Invantive SQL code copies all tables of a cloud application to a SQL Server database (or for instance PostgreSQL or Oracle). For each table in the cloud application, a stored procedure backup is executed which first creates the table in the target data container (assumed to be SQL Server), then loaded with the bulk loader and then indexes are added where deemed necessary:

create or replace procedure backup
( p_table_name                    varchar2
, p_provider_data_container_alias varchar2
)
is
--
-- Copy a table named A.B.C in data container D
-- to a SQL Server table named backup_a_b_c.
--
  l_stmt varchar2;
begin
  dbms_output.put_line('Start copy of ' || p_table_name || '.');
  l_stmt := 'create or replace table backup_' || replace(lower(p_table_name), '.', '_') || '@sqlserver'
  || ' as'
  || ' select /*+ http_disk_cache(true) http_memory_cache(false) */ *'
  || ' from   ' || p_table_name || '@'
  || p_provider_data_container_alias
  || ' src'
  ;
  execute immediate l_stmt;
  dbms_output.put_line('Copied rows from ' || p_table_name || '.');
exception
  when others
  then
    dbms_output.put_line('Exception on copy ' || p_table_name || ': ' || sqlerrm);
end;

begin
  for r in
  ( select call_safe_name
    ,      provider_data_container_alias
    from   SYSTEMTABLES@DataDictionary
    where  provider_name = 'ActiveCampaign'
    and    schema = 'V3'
    and    type in ('table', 'tablefunction')
    order
    by     call_safe_name
  )
  loop
    backup(r.call_safe_name, r.provider_data_container_alias);
  end loop;
end;

Preparation is to make sure a data container alias ‘sqlserver’ is assigned for a SQL Server database.