Filter the Clone Database

Before you begin

Create CloneDB.

About this task

You can use the FilteredDatabaseCopy utility to create an SQL script that filters CloneDB and deletes the rows that do not meet the filter criteria. By default, the FilteredDatabaseCopy utility uses the FiltersMaster.xml file to perform most of the database filtering. However, for additional filtering of CloneDB (for example, to filter the customized local history tables), you can add additional filter elements to a copy of the FiltersMaster.xml file, and then map the file to the FilteredDatabaseCopy utility. For information on the commands used to run the utility, refer to FilteredDatabaseCopy Utility.

Procedure

  1. Access SQL Server Management Studio (SSMS).
  2. Download the latest version of the FilteredDatabaseCopy.exe and FiltersMaster.xml files from the KB article number 000036043. These files are available in the FilteredDatabaseCopy-1.0.0.zip file in the KB article.
    Tip:
    You can search for the KB article here: https://digitalsupport.ge.com/.
  3. Create an SQL script by using the FilteredDatabaseCopy.exe file.
    Note:
    For more information, refer to the SSMS documentation.
    The following example uses Windows authentication to log in to SqlServerInstanceName:
    D:\Utilities\bin>FilteredDatabaseCopy.exe -d SqlServerInstanceName -s CloneDB 
    -t CloneDB -r 365 -f FiltersMaster.xml -o UpdateTables > <SQL Script Name>
    Note:
    • The SQL script in this example is configured to retain the data of 365 days preceding the current date.
    • You can also use SQL authentication using -UserName and -Password.
  4. Run the script.
    CloneDB is filtered.
    Note:
    • The time taken by the script to run depends on the size of CloneDB.
    • The script removes all the constraints in the database, and then adds them again. However, if there is any data that already exists in the database and violates a constraint, an error occurs while adding the constraint to the database.
  5. Verify that data is deleted in the SQL server. Using a SELECT query on the purged tables, verify the timestamp.
    The following message is an example of the query:
    SELECT MAX
          (End_Time), MIN (End_Time)     
    FROM [DatabaseName].[dbo].[Timed_Event_Details]
  6. If any error occurs when you run the script, perform the following steps to identify the source of the error, and then resolve the issue:
    1. Double-click the error message, and then scroll down until the RAISERROR line.
      The following message is an example of an SQL error message:
      Msg 50000, Level 11, State 0, Line 10
      Error with [xsdSchemaElements_FK_xsdSchemaElements] on line 4321: 
      Could not create constraint. See previous errors. Continuing..
      
    2. Above the RAISEERROR line, and between the BEGIN_TRY and END_TRY lines, locate the table and constraint names.
    3. Create a query for CloneDB using the following code example:
      ALTER TABLE <TableName> CHECK CONSTRAINT <ConstraintName>
    4. Execute the query to get detailed information about the error.
    5. Identify the source of the error from the detailed information, and as needed, modify the data in CloneDB to resolve the constraint violation.
    6. Execute the new query again.
      If the violation is successfully resolved, the constraint is enabled.

What to do next

Delete unreferenced rows in CloneDB.