3 Replies Latest reply on Jul 16, 2013 8:28 AM by Tim-KGS

    Production to Development

    Apprentice

      Hello,

       

      I would like to occasionally take my Production database and restore it to the Development server to ensure that any tests performed are on a solid duplicate of the Production system.

       

      We have distinct mailboxes available for both Production and Development.

       

      I was wondering if anyone has developed any scripts, either automated SQL or simple manual checklists to ensure that email addresses are changed everywhere (mail settings, templates, open cases, etc.)?

       

      Any other items that you are changing everytime you refresh Development with the Production data?

       

      I just need to ensure that the Development system isn't sending messages to end-users and causing confusion.

       

      Thanks!

        • 1. Re: Production to Development
          Expert

          This is what I use

           

           

          --//fix verity

          delete from tps_application_setting where tps_name = 'VeritySettings'

           

          --//update all email addresses in the user table

          UPDATE    tps_user

          SET              tps_email_address = 'DEV EMAIL ADDRESS'

          where tps_email_address <> 'DEV EMAIL ADDRESS' and tps_email_address is not null

           

          --//update all email addresses in emails waiting to be sent

          UPDATE    dbo.tps_user_message_recipient

          SET              tps_email_address = 'DEV EMAIL ADDRESS'

          where tps_email_address <> 'DEV EMAIL ADDRESS'

           

          --Delete all emails sent

          delete dbo.tps_user_message_recipient

           

           

          -- Set all passwords to test (apart from System, ServiceAccount and Guest)

          update dbo.tps_password

          set tps_password = 'C8-05-9E-2E-C7-41-9F-59-0E-79-D7-F1-B7-74-BF-E6'

          where tps_guid not in ('F14960BD-0669-40AF-8EB6-E587F9173B16','28475DCB-9449-4890-930B-28824007F09B','9D895D55-8DF7-48D1-B0B1-C6294A08DEF9')

           

           

          -- Update Outbound mail settings

          Update  tps_application_setting

          set tps_value = 'DEV EMAIL ADDRESS'

          where tps_Application_config_guid = 'F8669F32-51CD-43C4-9842-791333774F7C' and  tps_name = 'From'

           

          Update  tps_application_setting

          set tps_value = 'DEV EMAIL ADDRESS'

          where tps_Application_config_guid = 'F8669F32-51CD-43C4-9842-791333774F7C' and  tps_name = 'ReplyTo'

           

          Update  tps_application_setting

          set tps_value = 'DEV SMTP ADDRESS'

          where tps_Application_config_guid = 'F8669F32-51CD-43C4-9842-791333774F7C' and  tps_name = 'Server'

           

          -- Update Process Approver URL in System Settings - not sure if this works properly

          Update  tps_application_setting

          set tps_value = 'True'

          where  tps_Application_config_guid = 'C4A7BFFC-7093-44CA-8E99-8F4145158F18' and tps_name = 'WebAccessApproverURLEnabled'

           

          Update  tps_application_setting

          set tps_value = 'http://localhost/WebAccess/object/open.rails?class_name={Class/Module/Name}.{Class/Name}&key={Guid}'

          where tps_Application_config_guid = 'C4A7BFFC-7093-44CA-8E99-8F4145158F18' and tps_name = 'WebAccessApproverURL'

           

          --- All escalation notifications to an email address to go to dev mailbox instead.

          update lc_notification_recipient

          set lc_email = 'DEV EMAIL ADDRESS'

          where lc_email in ('xxx','xxx','xxx')

           

           

           

          --- set a value for Service.Name as this needs to be filled in for the MDM to complete ( I need to do this as I changed the Name field in Service to be non-mandatory and the MDM resets this everytime)

          update am_service

          set am_name = 'temp'

          where am_name is null

           

          --The one thing I've not figured out yet is how to set the process approver URL

          • 2. Re: Production to Development
            Apprentice

            Thank you very much Elizabeth!

             

            We're also using Request Processes that use different Reply Display Names and Reply E-Mail Addresses.  These are defined in Templates.  I still need to figure out if I can automate the change of these fields.

             

            Maybe someone else will help us out with setting the process approver URL?

            • 3. Re: Production to Development
              Apprentice

              We're also using Request Processes that use different Reply Display Names and Reply E-Mail Addresses.  These are defined in Templates.  I still need to figure out if I can automate the change of these fields.

               

              Elizabeth inspired me to start digging thru the database and I found that the values for my Request Template fields are in the table - lc_object_template_value and will require a replace on the lc_value field.

               

              I'll be testing this week...