KB002184

K2 Five Archiving Scripts zip file: Attached below

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

In K2 Five and K2 Platform Classic you can archive reporting data in K2 by running a set of SQL scripts. 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.

Steps

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

1. Determine your source database (K2).
2. Create a destination (archiving) database, for example K2Archive.
   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 edit the archive script in step 8.
3. Download and extract the attached zip file. 
4. Run the mArchiveBackupLog.sql script, located in the (1) Additional Database Archiving Scripts folder, 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. 
5. Locate the mArchive_(version).sql script that matches your version of K2, located in the (2) K2 SQL Server Database Archiving Scripts/K2 [your current version of K2] folder. Run the script on the K2 database. This script creates the mArchive Stored Procedure which is used to archive data. 
6. Open the startArchiving.sql script, located in the (1) Additional Database Archiving Scripts folder. 
7. If you have a different source database name, change the value for @logDB to the database name you identified in step1.
8. Change the value for @arcDB to the archive database name that you created in step 2.
9. Specify a date range on the @FromDate and @ToDate. Data within this range will be archived to the target archive database. 
10. 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 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

Archiving in a K2 blackpearl-K2 Five upgrade scenario

If you are upgrading to K2 Five from a previous version of K2 with archived data, follow the steps below:

1. If necessary, archive your existing environment's data using the applicable user interface for your version of K2. See KB000322: Archiving K2 blackpearl data   
2. Upgrade your environment to K2 blackpearl 4.7.
3. Restore archived data using the Legacy Workspace
4. Upgrade your environment to the latest version of K2 Five.
5. Use the procedure described in Steps above to archive your data in K2 Five. If you have existing archive databases for previous versions of K2, do not use them, but instead create a new database for the K2 Five archived data. 

Archiving a K2 SQL Azure database

1. Determine your source database (K2).
2. Download and extract the attached zip file.
3. Locate the mArchive.sql script that matches your version of K2, located in the (3) K2 SQL Azure Database Archiving Scripts/K2 [your current version of K2] folder. Run the script on the K2 database. This script creates the mArchive Stored Procedure which is used to archive data. 
4. Open the StartArchiving.sql script – specify the @FromDate and @ToDate values. Data within these date ranges will be archived.
5. 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 to view the progress of the archiving operation. A new Schema, ServerLogArchive, will be created where the archived data will be stored.
6. 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. 
• Archived records are permanently removed from the live K2 database and there is no way to restore those records back into the live database. Guidance on reporting on archived data will be provided at a future date.
• When running K2 Five databases on SQL Azure, archiving from K2 Workspace will not function.