-- [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!!!

-- Board
UPDATE [dbo].[ClientStorageData] SET [Value] = '"Board ' + CAST(StorageId AS VARCHAR(10)) + '"' WHERE [Key] = 'name'

-- General entities
UPDATE General SET [Description] = 'Description' + CAST(GeneralID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(GeneralID AS VARCHAR(10));
UPDATE BugHistory SET [Description] = 'Description' + CAST(BugID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(BugID AS VARCHAR(10));
UPDATE FeatureHistory SET [Description] = 'Description' + CAST(FeatureID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(FeatureID AS VARCHAR(10));
UPDATE ImpedimentHistory SET [Description] = 'Description' + CAST(ImpedimentID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(ImpedimentID AS VARCHAR(10));
UPDATE RequestHistory SET [Description] = 'Description' + CAST(RequestID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(RequestID AS VARCHAR(10));
UPDATE TaskHistory SET [Description] = 'Description' + CAST(TaskID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(TaskID AS VARCHAR(10));
UPDATE UserStoryHistory SET [Description] = 'Description' + CAST(UserStoryID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(UserStoryID 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 ReleaseHistory SET [Description] = 'Description' + CAST(ReleaseID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(ReleaseID AS VARCHAR(10));
UPDATE BuildHistory SET [Description] = 'Description' + CAST(BuildID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(BuildID AS VARCHAR(10));
UPDATE IterationHistory SET [Description] = 'Description' + CAST(IterationID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(IterationID AS VARCHAR(10));
UPDATE ProcessHistory SET [Description] = 'Description' + CAST(ProcessID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(ProcessID AS VARCHAR(10));
UPDATE ProgramHistory SET [Description] = 'Description' + CAST(ProgramID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(ProgramID AS VARCHAR(10));
UPDATE ProjectHistory SET [Description] = 'Description' + CAST(ProjectID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(ProjectID AS VARCHAR(10));
UPDATE [SquadIterationHistory] SET [Description] = 'Description' + CAST(SquadIterationID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(SquadIterationID AS VARCHAR(10));
UPDATE SquadHistory SET [Description] = 'Description' + CAST(SquadID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(SquadID AS VARCHAR(10));
    
--QA
UPDATE [TestCase] SET [Steps] = 'Steps' + CAST(TestCaseID AS VARCHAR(10)),
    [LastFailureComment] = 'Comment' + CAST(TestCaseID AS VARCHAR(10)),
    [Success] = 'Success' + CAST(TestCaseID AS VARCHAR(10));
UPDATE [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 [TestCaseRun] SET [Comment] = 'Comment' + CAST(TestCaseRunID AS VARCHAR(10));
UPDATE [TestCaseRunHistory] SET [Comment] = 'Comment' + CAST(TestCaseRunID AS VARCHAR(10));
UPDATE [TestPlanHistory] SET [Description] = 'Description' + CAST(TestPlanID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(TestPlanID AS VARCHAR(10));
UPDATE [TestPlanRunHistory] SET [Description] = 'Description' + CAST(TestPlanRunID AS VARCHAR(10)),
    [Name] = 'Name' + CAST(TestPlanRunID AS VARCHAR(10));
UPDATE [TestStep] SET [Description] = 'Description' + CAST(TestStepID AS VARCHAR(10)),
    [Result] = 'Result' + CAST(TestStepID AS VARCHAR(10));
UPDATE [TestStepHistory] SET [Description] = 'Description' + CAST(TestStepID AS VARCHAR(10)),
    [Result] = 'Result' + CAST(TestStepID AS VARCHAR(10));
    
-- Time
UPDATE [Time] SET [Description] = 'Description' + CAST(TimeID AS VARCHAR(10));
UPDATE [CustomActivity] SET [Name] = 'Name' + CAST(CustomActivityID AS VARCHAR(10));
UPDATE TimeHistory SET [Description] = 'Description' + CAST(TimeID AS VARCHAR(10));
UPDATE Time SET [CustomField1] = 'CustomField1' WHERE [CustomField1] IS NOT NULL
UPDATE Time SET [CustomField2] = 'CustomField2' WHERE [CustomField2] IS NOT NULL
UPDATE Time SET [CustomField3] = 'CustomField3' WHERE [CustomField3] IS NOT NULL
UPDATE Time SET [CustomField4] = 'CustomField4' WHERE [CustomField4] IS NOT NULL
UPDATE Time SET [CustomField5] = 'CustomField5' WHERE [CustomField5] IS NOT NULL
UPDATE Time SET [CustomField6] = 'CustomField6' WHERE [CustomField6] IS NOT NULL
UPDATE Time SET [CustomField7] = 'CustomField7' WHERE [CustomField7] IS NOT NULL
UPDATE Time SET [CustomField8] = 'CustomField8' WHERE [CustomField8] IS NOT NULL
UPDATE Time SET [CustomField9] = 'CustomField9' WHERE [CustomField9] IS NOT NULL
UPDATE Time SET [CustomField10] = 'CustomField10' WHERE [CustomField10] IS NOT NULL
UPDATE Time SET [CustomField11] = 'CustomField11' WHERE [CustomField11] IS NOT NULL
UPDATE Time SET [CustomField12] = 'CustomField12' WHERE [CustomField12] IS NOT NULL
UPDATE Time SET [CustomField13] = 'CustomField13' WHERE [CustomField13] IS NOT NULL
UPDATE Time SET [CustomField14] = 'CustomField14' WHERE [CustomField14] IS NOT NULL
UPDATE Time SET [CustomField15] = 'CustomField15' WHERE [CustomField15] IS NOT NULL
UPDATE Time SET [CustomField16] = 'CustomField16' WHERE [CustomField16] IS NOT NULL
UPDATE Time SET [CustomField17] = 'CustomField17' WHERE [CustomField17] IS NOT NULL
UPDATE Time SET [CustomField18] = 'CustomField18' WHERE [CustomField18] IS NOT NULL
UPDATE Time SET [CustomField19] = 'CustomField19' WHERE [CustomField19] IS NOT NULL
UPDATE Time SET [CustomField20] = 'CustomField20' WHERE [CustomField20] IS NOT NULL
UPDATE Time SET [CustomField21] = 'CustomField21' WHERE [CustomField21] IS NOT NULL
UPDATE Time SET [CustomField22] = 'CustomField22' WHERE [CustomField22] IS NOT NULL
UPDATE Time SET [CustomField23] = 'CustomField23' WHERE [CustomField23] IS NOT NULL
UPDATE Time SET [CustomField24] = 'CustomField24' WHERE [CustomField24] IS NOT NULL
UPDATE Time SET [CustomField25] = 'CustomField25' WHERE [CustomField25] IS NOT NULL
UPDATE Time SET [CustomField26] = 'CustomField26' WHERE [CustomField26] IS NOT NULL
UPDATE Time SET [CustomField27] = 'CustomField27' WHERE [CustomField27] IS NOT NULL
UPDATE Time SET [CustomField28] = 'CustomField28' WHERE [CustomField28] IS NOT NULL
UPDATE Time SET [CustomField29] = 'CustomField29' WHERE [CustomField29] IS NOT NULL
UPDATE Time SET [CustomField30] = 'CustomField30' WHERE [CustomField30] IS NOT NULL
UPDATE Time SET [CustomField31] = 'CustomField31' WHERE [CustomField31] IS NOT NULL
UPDATE Time SET [CustomField32] = 'CustomField32' WHERE [CustomField32] IS NOT NULL
UPDATE Time SET [CustomField33] = 'CustomField33' WHERE [CustomField33] IS NOT NULL
UPDATE Time SET [CustomField34] = 'CustomField34' WHERE [CustomField34] IS NOT NULL
UPDATE Time SET [CustomField35] = 'CustomField35' WHERE [CustomField35] IS NOT NULL
UPDATE Time SET [CustomField36] = 'CustomField36' WHERE [CustomField36] IS NOT NULL
UPDATE Time SET [CustomField37] = 'CustomField37' WHERE [CustomField37] IS NOT NULL
UPDATE Time SET [CustomField38] = 'CustomField38' WHERE [CustomField38] IS NOT NULL
UPDATE Time SET [CustomField39] = 'CustomField39' WHERE [CustomField39] IS NOT NULL
UPDATE Time SET [CustomField40] = 'CustomField40' WHERE [CustomField40] IS NOT NULL
UPDATE Time SET [CustomField41] = 'CustomField41' WHERE [CustomField41] IS NOT NULL
UPDATE Time SET [CustomField42] = 'CustomField42' WHERE [CustomField42] IS NOT NULL
UPDATE Time SET [CustomField43] = 'CustomField43' WHERE [CustomField43] IS NOT NULL
UPDATE Time SET [CustomField44] = 'CustomField44' WHERE [CustomField44] IS NOT NULL
UPDATE Time SET [CustomField45] = 'CustomField45' WHERE [CustomField45] IS NOT NULL
UPDATE Time SET [CustomField46] = 'CustomField46' WHERE [CustomField46] IS NOT NULL
UPDATE Time SET [CustomField47] = 'CustomField47' WHERE [CustomField47] IS NOT NULL
UPDATE Time SET [CustomField48] = 'CustomField48' WHERE [CustomField48] IS NOT NULL
UPDATE Time SET [CustomField49] = 'CustomField49' WHERE [CustomField49] IS NOT NULL
UPDATE Time SET [CustomField50] = 'CustomField50' WHERE [CustomField50] IS NOT NULL
UPDATE Time SET [CustomField51] = 'CustomField51' WHERE [CustomField51] IS NOT NULL
UPDATE Time SET [CustomField52] = 'CustomField52' WHERE [CustomField52] IS NOT NULL
UPDATE Time SET [CustomField53] = 'CustomField53' WHERE [CustomField53] IS NOT NULL
UPDATE Time SET [CustomField54] = 'CustomField54' WHERE [CustomField54] IS NOT NULL
UPDATE Time SET [CustomField55] = 'CustomField55' WHERE [CustomField55] IS NOT NULL
UPDATE Time SET [CustomField56] = 'CustomField56' WHERE [CustomField56] IS NOT NULL
UPDATE Time SET [CustomField57] = 'CustomField57' WHERE [CustomField57] IS NOT NULL
UPDATE Time SET [CustomField58] = 'CustomField58' WHERE [CustomField58] IS NOT NULL
UPDATE Time SET [CustomField59] = 'CustomField59' WHERE [CustomField59] IS NOT NULL
UPDATE Time SET [CustomField60] = 'CustomField60' WHERE [CustomField60] IS NOT NULL

-- Users
UPDATE [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 [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 [MessageUid] SET [MailServer] = 'Server',
    [MailLogin] = 'Login';
UPDATE Project SET SCPassword = '', InboundMailPassword= '';
DELETE FROM TpProfile WHERE PropertyName LIKE '%InboundMailSettings';

-- Settings
UPDATE PluginProfile SET Settings = '', ProfileName = '';
UPDATE [GlobalSetting] SET [CompanyName] = 'Company',
    [DisableHttpAccess] = 0,
    [SMTPServer] = 'Server',
    [SMTPLogin] = 'Login',
    [SMTPPassword] = 'Password';

UPDATE [Tag] SET [Name] = 'Name' + CAST(TagID AS VARCHAR(10));
UPDATE [TagBundle] SET [Name] = 'Name' + CAST(TagBundleID AS VARCHAR(10));
UPDATE [AttachmentFile] SET [Buffer] = NULL;
UPDATE [Company] SET [CompanyName] = 'Company' + CAST(CompanyID AS VARCHAR(10)),
    [CompanyUrl] = 'http://' + CAST(CompanyID AS VARCHAR(10));
UPDATE [Milestone] SET [Description] = 'Description' + CAST(MilestoneId AS VARCHAR(10)),
    [Name] = 'Name' + CAST(MilestoneId AS VARCHAR(10));
UPDATE [Revision] SET [Description] = 'Description' + CAST(RevisionID AS VARCHAR(10))
UPDATE [RevisionFile] SET [FileName] = '/file.txt'

-- Custom fields
UPDATE General SET [CustomField1] = 'CustomField1' WHERE [CustomField1] IS NOT NULL
UPDATE General SET [CustomField2] = 'CustomField2' WHERE [CustomField2] IS NOT NULL
UPDATE General SET [CustomField3] = 'CustomField3' WHERE [CustomField3] IS NOT NULL
UPDATE General SET [CustomField4] = 'CustomField4' WHERE [CustomField4] IS NOT NULL
UPDATE General SET [CustomField5] = 'CustomField5' WHERE [CustomField5] IS NOT NULL
UPDATE General SET [CustomField6] = 'CustomField6' WHERE [CustomField6] IS NOT NULL
UPDATE General SET [CustomField7] = 'CustomField7' WHERE [CustomField7] IS NOT NULL
UPDATE General SET [CustomField8] = 'CustomField8' WHERE [CustomField8] IS NOT NULL
UPDATE General SET [CustomField9] = 'CustomField9' WHERE [CustomField9] IS NOT NULL
UPDATE General SET [CustomField10] = 'CustomField10' WHERE [CustomField10] IS NOT NULL
UPDATE General SET [CustomField11] = 'CustomField11' WHERE [CustomField11] IS NOT NULL
UPDATE General SET [CustomField12] = 'CustomField12' WHERE [CustomField12] IS NOT NULL
UPDATE General SET [CustomField13] = 'CustomField13' WHERE [CustomField13] IS NOT NULL
UPDATE General SET [CustomField14] = 'CustomField14' WHERE [CustomField14] IS NOT NULL
UPDATE General SET [CustomField15] = 'CustomField15' WHERE [CustomField15] IS NOT NULL
UPDATE General SET [CustomField16] = 'CustomField16' WHERE [CustomField16] IS NOT NULL
UPDATE General SET [CustomField17] = 'CustomField17' WHERE [CustomField17] IS NOT NULL
UPDATE General SET [CustomField18] = 'CustomField18' WHERE [CustomField18] IS NOT NULL
UPDATE General SET [CustomField19] = 'CustomField19' WHERE [CustomField19] IS NOT NULL
UPDATE General SET [CustomField20] = 'CustomField20' WHERE [CustomField20] IS NOT NULL
UPDATE General SET [CustomField21] = 'CustomField21' WHERE [CustomField21] IS NOT NULL
UPDATE General SET [CustomField22] = 'CustomField22' WHERE [CustomField22] IS NOT NULL
UPDATE General SET [CustomField23] = 'CustomField23' WHERE [CustomField23] IS NOT NULL
UPDATE General SET [CustomField24] = 'CustomField24' WHERE [CustomField24] IS NOT NULL
UPDATE General SET [CustomField25] = 'CustomField25' WHERE [CustomField25] IS NOT NULL
UPDATE General SET [CustomField26] = 'CustomField26' WHERE [CustomField26] IS NOT NULL
UPDATE General SET [CustomField27] = 'CustomField27' WHERE [CustomField27] IS NOT NULL
UPDATE General SET [CustomField28] = 'CustomField28' WHERE [CustomField28] IS NOT NULL
UPDATE General SET [CustomField29] = 'CustomField29' WHERE [CustomField29] IS NOT NULL
UPDATE General SET [CustomField30] = 'CustomField30' WHERE [CustomField30] IS NOT NULL
UPDATE General SET [CustomField31] = 'CustomField31' WHERE [CustomField31] IS NOT NULL
UPDATE General SET [CustomField32] = 'CustomField32' WHERE [CustomField32] IS NOT NULL
UPDATE General SET [CustomField33] = 'CustomField33' WHERE [CustomField33] IS NOT NULL
UPDATE General SET [CustomField34] = 'CustomField34' WHERE [CustomField34] IS NOT NULL
UPDATE General SET [CustomField35] = 'CustomField35' WHERE [CustomField35] IS NOT NULL
UPDATE General SET [CustomField36] = 'CustomField36' WHERE [CustomField36] IS NOT NULL
UPDATE General SET [CustomField37] = 'CustomField37' WHERE [CustomField37] IS NOT NULL
UPDATE General SET [CustomField38] = 'CustomField38' WHERE [CustomField38] IS NOT NULL
UPDATE General SET [CustomField39] = 'CustomField39' WHERE [CustomField39] IS NOT NULL
UPDATE General SET [CustomField40] = 'CustomField40' WHERE [CustomField40] IS NOT NULL
UPDATE General SET [CustomField41] = 'CustomField41' WHERE [CustomField41] IS NOT NULL
UPDATE General SET [CustomField42] = 'CustomField42' WHERE [CustomField42] IS NOT NULL
UPDATE General SET [CustomField43] = 'CustomField43' WHERE [CustomField43] IS NOT NULL
UPDATE General SET [CustomField44] = 'CustomField44' WHERE [CustomField44] IS NOT NULL
UPDATE General SET [CustomField45] = 'CustomField45' WHERE [CustomField45] IS NOT NULL
UPDATE General SET [CustomField46] = 'CustomField46' WHERE [CustomField46] IS NOT NULL
UPDATE General SET [CustomField47] = 'CustomField47' WHERE [CustomField47] IS NOT NULL
UPDATE General SET [CustomField48] = 'CustomField48' WHERE [CustomField48] IS NOT NULL
UPDATE General SET [CustomField49] = 'CustomField49' WHERE [CustomField49] IS NOT NULL
UPDATE General SET [CustomField50] = 'CustomField50' WHERE [CustomField50] IS NOT NULL
UPDATE General SET [CustomField51] = 'CustomField51' WHERE [CustomField51] IS NOT NULL
UPDATE General SET [CustomField52] = 'CustomField52' WHERE [CustomField52] IS NOT NULL
UPDATE General SET [CustomField53] = 'CustomField53' WHERE [CustomField53] IS NOT NULL
UPDATE General SET [CustomField54] = 'CustomField54' WHERE [CustomField54] IS NOT NULL
UPDATE General SET [CustomField55] = 'CustomField55' WHERE [CustomField55] IS NOT NULL
UPDATE General SET [CustomField56] = 'CustomField56' WHERE [CustomField56] IS NOT NULL
UPDATE General SET [CustomField57] = 'CustomField57' WHERE [CustomField57] IS NOT NULL
UPDATE General SET [CustomField58] = 'CustomField58' WHERE [CustomField58] IS NOT NULL
UPDATE General SET [CustomField59] = 'CustomField59' WHERE [CustomField59] IS NOT NULL
UPDATE General SET [CustomField60] = 'CustomField60' WHERE [CustomField60] IS NOT NULL