-- [Target Process] Confidential Data Removal Script -- If you're going to send a backup of your database to TP support -- and want to remove all the sensitive values from your backup -- please run this script against a BACKUP of your database!!! EXEC disableTriggers; -- Board UPDATE [dbo].[ClientStorageData] SET [Value] = '"Board ' + CAST(StorageId AS VARCHAR(10)) + '"' WHERE [Key] = 'name' -- General entities UPDATE [dbo].[General] SET [Description] = 'Description' + CAST(GeneralID AS VARCHAR(10)), [Name] = 'Name' + CAST(GeneralID AS VARCHAR(10)); UPDATE [dbo].[BugHistory] SET [Description] = 'Description' + CAST(BugID AS VARCHAR(10)), [Name] = 'Name' + CAST(BugID AS VARCHAR(10)); UPDATE [dbo].[FeatureHistory] SET [Description] = 'Description' + CAST(FeatureID AS VARCHAR(10)), [Name] = 'Name' + CAST(FeatureID AS VARCHAR(10)); UPDATE [dbo].[ImpedimentHistory] SET [Description] = 'Description' + CAST(ImpedimentID AS VARCHAR(10)), [Name] = 'Name' + CAST(ImpedimentID AS VARCHAR(10)); UPDATE [dbo].[RequestHistory] SET [Description] = 'Description' + CAST(RequestID AS VARCHAR(10)), [Name] = 'Name' + CAST(RequestID AS VARCHAR(10)); UPDATE [dbo].[TaskHistory] SET [Description] = 'Description' + CAST(TaskID AS VARCHAR(10)), [Name] = 'Name' + CAST(TaskID AS VARCHAR(10)); UPDATE [dbo].[UserStoryHistory] SET [Description] = 'Description' + CAST(UserStoryID AS VARCHAR(10)), [Name] = 'Name' + CAST(UserStoryID AS VARCHAR(10)); UPDATE [dbo].[EpicHistory] SET [Description] = 'Description' + CAST(EpicID AS VARCHAR(10)), [Name] = 'Name' + CAST(EpicID AS VARCHAR(10)); UPDATE Comment SET [Description] = 'Description' + CAST(CommentID AS VARCHAR(10)); UPDATE CommentHistory SET [Description] = 'Description' + CAST(CommentID AS VARCHAR(10)); -- Planning UPDATE [dbo].[ReleaseHistory] SET [Description] = 'Description' + CAST(ReleaseID AS VARCHAR(10)), [Name] = 'Name' + CAST(ReleaseID AS VARCHAR(10)); UPDATE [dbo].[BuildHistory] SET [Description] = 'Description' + CAST(BuildID AS VARCHAR(10)), [Name] = 'Name' + CAST(BuildID AS VARCHAR(10)); UPDATE [dbo].[IterationHistory] SET [Description] = 'Description' + CAST(IterationID AS VARCHAR(10)), [Name] = 'Name' + CAST(IterationID AS VARCHAR(10)); UPDATE [dbo].[ProcessHistory] SET [Description] = 'Description' + CAST(ProcessID AS VARCHAR(10)), [Name] = 'Name' + CAST(ProcessID AS VARCHAR(10)); UPDATE [dbo].[ProgramHistory] SET [Description] = 'Description' + CAST(ProgramID AS VARCHAR(10)), [Name] = 'Name' + CAST(ProgramID AS VARCHAR(10)); UPDATE [dbo].[ProjectHistory] SET [Description] = 'Description' + CAST(ProjectID AS VARCHAR(10)), [Name] = 'Name' + CAST(ProjectID AS VARCHAR(10)); UPDATE [dbo].[SquadIterationHistory] SET [Description] = 'Description' + CAST(SquadIterationID AS VARCHAR(10)), [Name] = 'Name' + CAST(SquadIterationID AS VARCHAR(10)); UPDATE [dbo].[SquadHistory] SET [Description] = 'Description' + CAST(SquadID AS VARCHAR(10)), [Name] = 'Name' + CAST(SquadID AS VARCHAR(10)); --QA UPDATE [dbo].[TestCase] SET [Steps] = 'Steps' + CAST(TestCaseID AS VARCHAR(10)), [LastFailureComment] = 'Comment' + CAST(TestCaseID AS VARCHAR(10)), [Success] = 'Success' + CAST(TestCaseID AS VARCHAR(10)); UPDATE [dbo].[TestCaseHistory] SET [Description] = 'Description' + CAST(TestCaseID AS VARCHAR(10)), [Steps] = 'Steps' + CAST(TestCaseID AS VARCHAR(10)), [Success] = 'Success' + CAST(TestCaseID AS VARCHAR(10)), [LastFailureComment] = 'Comment' + CAST(TestCaseID AS VARCHAR(10)), [Name] = 'Name' + CAST(TestCaseID AS VARCHAR(10)); UPDATE [dbo].[TestCaseRun] SET [Comment] = 'Comment' + CAST(TestCaseRunID AS VARCHAR(10)); UPDATE [dbo].[TestCaseRunHistory] SET [Comment] = 'Comment' + CAST(TestCaseRunID AS VARCHAR(10)); UPDATE [dbo].[TestPlanHistory] SET [Description] = 'Description' + CAST(TestPlanID AS VARCHAR(10)), [Name] = 'Name' + CAST(TestPlanID AS VARCHAR(10)); UPDATE [dbo].[TestPlanRunHistory] SET [Description] = 'Description' + CAST(TestPlanRunID AS VARCHAR(10)), [Name] = 'Name' + CAST(TestPlanRunID AS VARCHAR(10)); UPDATE [dbo].[TestStep] SET [Description] = 'Description' + CAST(TestStepID AS VARCHAR(10)), [Result] = 'Result' + CAST(TestStepID AS VARCHAR(10)); UPDATE [dbo].[TestStepHistory] SET [Description] = 'Description' + CAST(TestStepID AS VARCHAR(10)), [Result] = 'Result' + CAST(TestStepID AS VARCHAR(10)); UPDATE [dbo].[FreezedTestCaseInfo] SET [Description] = 'Description' + REPLICATE(CAST('X' AS varchar(MAX)), LEN(Description)); UPDATE [dbo].[FreezedTestStepInfo] SET [Description] = 'Description' + REPLICATE(CAST('X' AS varchar(MAX)), LEN(Description)); -- Time UPDATE [dbo].[Time] SET [Description] = 'Description' + CAST(TimeID AS VARCHAR(10)); UPDATE [dbo].[CustomActivity] SET [Name] = 'Name' + CAST(CustomActivityID AS VARCHAR(10)); UPDATE [dbo].[TimeHistory] SET [Description] = 'Description' + CAST(TimeID AS VARCHAR(10)); -- Users UPDATE [dbo].[TpUser] SET [FirstName] = 'FirstName' + CAST(UserID AS VARCHAR(10)), [LastName] = 'LastName' + CAST(UserID AS VARCHAR(10)), [Email] = 'Email' + CAST(UserID AS VARCHAR(10)) + '@web.com', [Login] = 'Login' + CAST(UserID AS VARCHAR(10)) + '@web.com', [SecretWord] = 'SecretWord' + CAST(UserID AS VARCHAR(10)), [ActiveDirectoryName] = 'ActiveDirectoryName' + CAST(UserID AS VARCHAR(10)), [Phone] = 'Phone' + CAST(UserID AS VARCHAR(10)), [Notes] = 'Notes' + CAST(UserID AS VARCHAR(10)); -- Email Messages UPDATE [dbo].[Message] SET [Subject] = 'Subject' + CAST(MEssageID AS VARCHAR(10)), [Recipients] = 'Email' + CAST(MEssageID AS VARCHAR(10)) + '@web.com', [body] = 'body' + CAST(MEssageID AS VARCHAR(10)); UPDATE [dbo].[MessageUid] SET [MailServer] = 'Server', [MailLogin] = 'Login'; UPDATE [dbo].[Project] SET SCPassword = '', InboundMailPassword= ''; DELETE FROM [dbo].[TpProfile] WHERE PropertyName LIKE '%InboundMailSettings'; -- Settings UPDATE [dbo].[PluginProfile] SET Settings = '', ProfileName = ''; UPDATE [dbo].[GlobalSetting] SET [CompanyName] = 'Company', [DisableHttpAccess] = 0, [SMTPServer] = 'Server', [SMTPLogin] = 'Login', [SMTPPassword] = 'Password'; UPDATE [dbo].[Tag] SET [Name] = 'Name' + CAST(TagID AS VARCHAR(10)); UPDATE [dbo].[AttachmentFile] SET [Buffer] = NULL; UPDATE [dbo].[Company] SET [CompanyName] = 'Company' + CAST(CompanyID AS VARCHAR(10)), [CompanyUrl] = 'http://' + CAST(CompanyID AS VARCHAR(10)); UPDATE [dbo].[Milestone] SET [Description] = 'Description' + CAST(MilestoneId AS VARCHAR(10)), [Name] = 'Name' + CAST(MilestoneId AS VARCHAR(10)); UPDATE [dbo].[Revision] SET [Description] = 'Description' + CAST(RevisionID AS VARCHAR(10)) UPDATE [dbo].[RevisionFile] SET [FileName] = '/file.txt' EXEC enableTriggers;