How to manually archive data from the K2 database in K2 4.7

For information on archiving in K2 Five, please see the article KB002184: Archiving data in K2 Five

Archiving data in K2 4.7 is available in K2 Workspace. In the event where it can take a long time to archive the data, you can use the attached scripts and the procedure described below to archive reporting data.

The script archives data from the K2 database to another user-specified database, and includes verbose logging and information about the data it is currently processing. The script sends the logging information to the SQL output window, showing which table is currently being processed, how many records are affected, their timestamps and when a section is complete. This logging is not configurable and is not saved -- it is live output data which gives you a better idea of how the archiving is progressing. This is helpful for first-time archiving as it can take a long time and might appear stalled.

  • You must have access to the K2 database and able to execute SQL statements against the database. You may need to enlist the assistance of your database administrator to run the scripts provided.
  • You should make a backup of the K2 database before running the archive script.
  • If you need assistance in archiving your K2 reporting data, please log a support ticket with Product Support. 

    Steps

    Follow the steps below to create the necessary archive scripts and to execute the archive script. 

    1. Download and extract the attached zip file. 
    2. Run the mArchiveBackupLog.sql script against the K2 database. This script creates the mArchiveBackupLog Stored Procedure, which is used to output messages to the query window when you run the archive script. 
    3. Run the mArchive_v1.5.4.sql script on the K2 database. This script creates the mArchive Stored Procedure which is used to archive data. 
    4. If necessary, create a new archive database. (In the attached scripts, the archive database is called K2Archive). This is the target database and will be used as the repository for the archived data.
      If you have archived K2 data before and want to append additional archived data to the existing archive database, you do not need to create a new database; just use the name of the existing archive database when you execute the archive script in step 8.
    5. Open the startArchiving.sql script.
    6. Change the value for @arcDB to the archive database name that you created in step 4.
    7. Specify a date range on the @FromDate and @ToDate. Data within this range will be archived to the target archive database. 
    8. Run the startArchiving.sql script. Note that this script may take some time to complete, depending on the amount of data in your K2 Five environment. Use the Messages tab in the SQL query too to view the progress of the archiving operation. When the script is completed, you should see a message in the Messages window along the lines of Archiving completed successfully ALL DONE

      Considerations

      • Only K2 workflow reporting data will be archived. 
      • The archive scripts will only archive data for workflows in Completed state. If any workflows in the specified period for archiving are in another state (such as Active, Running or Error), the reporting data for these workflow instances will not be archived.