Filter the Clone Database
Before you begin
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
- Access SQL Server Management Studio (SSMS).
- 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/.
- Create an SQL script by using the FilteredDatabaseCopy.exe file.Note:The following example uses Windows authentication to log in to SqlServerInstanceName:For more information, refer to the SSMS documentation.
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
.
- 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.
- 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]
- 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: