Truncate all non-system data from HEAT DB for security reasons.  Leave structure only.

Version 1

    Details

    Truncate all non-system data from HEAT DB for security reasons.  Leave structure only.


    Resolution

     

    The purpose of this article is to outline a possible way to export the structure of a HEAT database without including any customer data.  This process should ONLY be run against a copy of the Production database.  Once the process is complete, the Customer's Admin should be able to login to the Admin module and export a .haf file of this copied DB that will only include the structure of the DB along with meta-data specific to allowing logging in and inspection of the database's construct.  All customer specific information and validation table data will have been truncated.

    Perform the below steps ONLY on a COPY of the Production database:
    1.  Launch SQL Management Studio.
    2.  Paste the following query setting the Query>> Results To value to "Results to Text".
    USE Your_DB_Name
    GO 
    SELECT 'TRUNCATE TABLE' + ' ' + NAME
    FROM SYS.Tables
    WHERE Name NOT LIKE 'HEAT%'
    AND Name NOT IN ('Tracker', 'TeamDefs', 'TeamMems')
    ORDER BY Name
    GO
    3.  This will give  you a TRUNCATE TABLE statement for all the tables in the HEAT database that are not related to function and/or ability to logging in.
    4.  Copy this list into a New Query window and execute.
    5.  Login to the Admin Module and export the .haf file.

    NOTE:  If certain validation information needs to be kept for troubleshooting (like Status, Priority, Assignee, etc.) those table(s) can be pulled out of the TRUNCATE TABLE list prior to running.  Otherwise, all validation table data will need to be recreated on the fly during troubleshooting.