Introduction
Here is a script that can be run in SQL Server Management Studio to prepare a Kepion Application Database for use as a template. The script will remove the following:
- AD user information
- Activity and Logging information
- Runtime APPs information
- Historic metadata information
Script
Attention: This script will PERMANENTLY alter and delete data from an application. DO NOT USE THIS ON A PRODUCTION SERVER OR DATABASE. Always verify that a functional backup exists before altering any of the data in these tables.
-- CLEAN UP MODELER
DELETE FROM [dbo].[HistoricForms]
DELETE FROM [dbo].[HistoricRules]
DELETE FROM [dbo].[ProcessDetails]
DELETE FROM [dbo].[ApplicationDeployment]
IF OBJECT_ID(N'[dbo].[ApplicationDefinedSets]') IS NOT NULL
BEGIN
UPDATE [dbo].[ApplicationDefinedSets] SET [ModifiedBy] = -2
END
UPDATE [dbo].[ApplicationDrillthroughDefinitions] SET [ModifiedBy] = -2
UPDATE [dbo].[ApplicationMemberVariables] SET [ModifiedBy] = -2
UPDATE [dbo].[ApplicationVariables] SET [ModifiedBy] = -2
UPDATE [dbo].[DashboardForms] SET [ModifiedBy] = -2
UPDATE [dbo].[DashboardPages] SET [ModifiedBy] = -2
UPDATE [dbo].[Dashboards] SET [ModifiedBy] = -2
UPDATE [dbo].[Dimensions] SET [ModifiedBy] = -2
UPDATE [dbo].[FileStores] SET [ModifiedBy] = -2
UPDATE [dbo].[Forms] SET [ModifiedBy] = -2
UPDATE [dbo].[Forms] SET [CreatedBy] = -2
UPDATE [dbo].[Hierarchies] SET [ModifiedBy] = -2
UPDATE [dbo].[HistoricForms] SET [ModifiedBy] = -2
UPDATE [dbo].[HistoricForms] SET [CreatedBy] = -2
UPDATE [dbo].[HistoricRules] SET [ModifiedBy] = -2
UPDATE [dbo].[MappingFilters] SET [ModifiedBy] = -2
UPDATE [dbo].[ModelDimensions] SET [ModifiedBy] = -2
UPDATE [dbo].[Models] SET [ModifiedBy] = -2
UPDATE [dbo].[Rules] SET [ModifiedBy] = -2
UPDATE [dbo].[Scripts] SET [ModifiedBy] = -2
IF OBJECT_ID(N'[dbo].[Partitions]', N'U') IS NOT NULL
BEGIN
UPDATE [dbo].[Partitions] SET [ModifiedBy] = -2
END
-- CLEAN UP ADMIN
DELETE FROM [dbo].[AuthorizationsUsers] WHERE [UserID] IN (SELECT [UserID] FROM [dbo].[Users] WHERE Type IN (1,2,5,6))
DELETE FROM [dbo].[AuthorizationsPageRestrictions] WHERE [UserID] IN (SELECT [UserID] FROM [dbo].[Users] WHERE Type IN (1,2,5,6))
DELETE FROM [dbo].[AuthorizationsFormRestrictions] WHERE [UserID] IN (SELECT [UserID] FROM [dbo].[Users] WHERE Type IN (1,2,5,6))
DELETE FROM [dbo].[UserAffiliations] WHERE [UserID] IN (SELECT [UserID] FROM [dbo].[Users] WHERE Type IN (1,2,5,6))
DELETE FROM [dbo].[ExtendedModelAccesses]
DELETE FROM [dbo].[ExtendedDimensionPermissions]
DELETE FROM [dbo].[DimensionDataPermissions] WHERE [UserID] IN (SELECT [UserID] FROM [dbo].[Users] WHERE Type IN (1,2,5,6))
DELETE FROM [dbo].[ModelAccesses] WHERE [UserID] IN (SELECT [UserID] FROM [dbo].[Users] WHERE Type IN (1,2,5,6))
DELETE FROM [dbo].[ApproverAssignments] WHERE [UserID] IN (SELECT [UserID] FROM [dbo].[Users] WHERE Type IN (1,2,5,6))
DELETE FROM [dbo].[AppointedApprovers]
UPDATE [dbo].[Authorizations] SET [CreatedBy] = -1
UPDATE [dbo].[Authorizations] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsDashboardRules] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsFilterGroups] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsFilterGroupsToForms] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsFormRestrictions] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsForms] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsNotificationTemplates] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsPageRestrictions] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsRules] SET [ModifiedBy] = -1
UPDATE [dbo].[AuthorizationsUsers] SET [ModifiedBy] = -1
UPDATE [dbo].[Submissions] SET [CreatedBy] = -1
UPDATE [dbo].[DimensionDataPermissions] SET [ModifiedBy] = -1
UPDATE [dbo].[ModelAccesses] SET [ModifiedBy] = -1
UPDATE [dbo].[UserAffiliations] SET [ModifiedBy] = -1
UPDATE [dbo].[Users] SET [ModifiedBy] = -1
UPDATE [dbo].[Htmls] SET [ModifiedBy] = -1
-- CLEAN UP LEGACY AUTHORIZATIONS
DELETE AF
FROM [dbo].[AuthorizationsForms] AF
INNER JOIN [dbo].[Authorizations] A ON AF.[AuthorizationID] = A.[AuthorizationID]
WHERE A.[Deleted] = 1
DELETE AF
FROM [dbo].[AuthorizationsRules] AF
INNER JOIN [dbo].[Authorizations] A ON AF.[AuthorizationID] = A.[AuthorizationID]
WHERE A.[Deleted] = 1
DELETE AF
FROM [dbo].[AuthorizationsDashboardRules] AF
INNER JOIN [dbo].[Authorizations] A ON AF.[AuthorizationID] = A.[AuthorizationID]
WHERE A.[Deleted] = 1
DELETE AF
FROM [dbo].[AuthorizationsUsers] AF
INNER JOIN [dbo].[Authorizations] A ON AF.[AuthorizationID] = A.[AuthorizationID]
WHERE A.[Deleted] = 1
DELETE FROM [dbo].[Authorizations]
WHERE [Deleted] = 1
-- CLEAN UP ACTIVITYID FROM FACTS
DECLARE @Statement NVARCHAR(MAX);
DECLARE Statement_Cursor CURSOR
FOR SELECT N'UPDATE [' + name + N'] SET [ActivityID] = NULL' FROM sys.tables WHERE Name LIKE 'F[_]%[_]Writeback'
OPEN Statement_Cursor
FETCH NEXT FROM Statement_Cursor INTO @Statement
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
EXEC(@Statement)
FETCH NEXT FROM Statement_Cursor INTO @Statement
END
CLOSE Statement_Cursor
DEALLOCATE Statement_Cursor
DECLARE Statement_Cursor CURSOR
FOR SELECT N'UPDATE [' + name + N'] SET [ActivityID] = NULL, [InitiatedActivityID] = NULL' FROM sys.tables WHERE Name like 'FH[_]%[_]Writeback'
OPEN Statement_Cursor
FETCH NEXT FROM Statement_Cursor INTO @Statement
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
EXEC(@Statement)
FETCH NEXT FROM Statement_Cursor INTO @Statement
END
CLOSE Statement_Cursor
DEALLOCATE Statement_Cursor
-- CLEAN UP RUNTIME INFORMATION
DELETE FROM [dbo].[Filter_Planning]
DELETE FROM [dbo].[Formula_CoordinateDefinition]
DELETE FROM [dbo].[Formula_Expression]
DELETE FROM [dbo].[Formula_RHSTupleReference]
DELETE FROM [dbo].[Formula_UniqueName]
DELETE FROM [dbo].[Activities]
DELETE FROM [dbo].[Submissions]
DELETE FROM [dbo].[Session]
DELETE FROM [dbo].[LoggingDashboardPageAccess]
IF OBJECT_ID(N'[dbo].[RuleComponentProgress]') IS NOT NULL
BEGIN
DELETE FROM [dbo].[RuleComponentProgress]
END
IF OBJECT_ID(N'[dbo].[RuleProgress]') IS NOT NULL
BEGIN
DELETE FROM [dbo].[RuleProgress]
END
-- FINALLY, CLEAN UP USERS (ANY AD USER\GROUP)
DELETE FROM [dbo].[Users]
WHERE [Type] IN (1,2,5,6)