4 Replies Latest reply on Mar 4, 2016 6:40 PM by NickLightbody

    Moving scripts and other database structures between a dev db and production db

    douglerner

      What are best practices for moving scripts and other database structures between a dev db and production db?

       

      You don't want to just copy over the dev db because the data is different.

       

      I understand if you have FM Advanced you can copy scripts and paste them over. But does that have to be done script by script? How do you keep track of what changed and what didn't?

       

      And what about table management, like changing field names, or relationship tables? Do you need to remember all the changes and then make the same changes at production? How do you keep track?

       

      Or is there some way of exporting just the "structure" of a database and importing it to production, without copying over the data?

       

      Thanks,

       

      doug

        • 1. Re: Moving scripts and other database structures between a dev db and production db
          nicolai

          There is no Telerik or any source control for FileMaker, if this is what you are looking for.  FileMaker Pro Advanced will let you import scripts, but it will not update the existing ones.

           

          Data Separation model is trying to deal with deployment, try to research on this.

           

          You can try refreshfm (RefreshFM | Goya Pty Ltd), but this is a paid application.

           

          Nicolai

          • 2. Re: Moving scripts and other database structures between a dev db and production db
            Mike_Mitchell

            Generally, I try not to copy / paste for updates. Rather, I find it safer (because I'm getting older) to clone my development copy and then import the records to it from production (after shutting down the production database to prevent drift).

             

            nicolai has already let you know about RefreshFM, which can automate your updates. Most of us who have been doing this a while have developed our own home-grown migration strategies. I do recommend some form of automation here; it's just too easy to forget something otherwise. Some people like to build a system that updates from the production database while it's still hosted (I went to a DevCon presentation last year where this was the strategy). I just shut it down and run a set of scripts that does these tasks:

             

            1) Show all records in all affected tables in the old copy.

            2) Import from those tables to the new copy.

             

            There's an optional 1a) step, which basically deletes all the records in the affected tables in the new copy rather than using a clone. Sometimes, a clone is a little problematic if you have Settings tables that you (the developer) control and you have new stuff you want in those tables in production (as opposed to using what's already there). But a clone is still a good practice for stability.

             

            douglerner wrote:

             

            And what about table management, like changing field names, or relationship tables? Do you need to remember all the changes and then make the same changes at production? How do you keep track?

             

             

            No, if you just use a copy of the dev database, then all this comes over with it. The only thing you have to watch out for is, if you use "matching names" on import, it can break if you change field names. I generally leave the dialogs up and double-check it while it's performing the imports as a self-check.

             

            HTH

             

            Mike

            • 3. Re: Moving scripts and other database structures between a dev db and production db
              wimdecorte

              douglerner wrote:

               

              I understand if you have FM Advanced you can copy scripts and paste them over. But does that have to be done script by script? How do you keep track of what changed and what didn't?

               

               

              You keep track of it manually and you can double-check it by running a diff report with something like fmDiff or BaseElements.

               

              Then you redo your changes in the live system or you make your dev system live and import the data.  We often have to do one or the other, redoing the changes when the system is under active development by other teams while we code a new module or a bigger update to existing functionality.

               

              When you redo your schema changes, keep in mind that there is a definite order of operation to follow to get it right.

               

                   

              Table
              File Reference
              Field
              Table Occurence
              Custom Function
              Layout Stub (empty layout but with correct layout name)
              Value List
              Custom Menu Set
              Field Def settings (AE, validation, repeats...)
              Script
              Layout Content
              Priv Set
              Data

               

               

              Priv Set can come earlier if you use its name in scripts.

              • 4. Re: Moving scripts and other database structures between a dev db and production db
                NickLightbody

                Adding to Wim's guidance:

                 

                If you are sensible with custom functions and only use parameter inputs, i.e. never directly reference a field within the function, then Custom Functions should come before field since a field may often use a custom function.

                 

                I generally copy them over as the very first item.

                 

                You should remember that FM works great if the table occurrence names are the same in both versions, it is the one area of FM where a "label" change will break things, where the internal id isn't used to match items and it works off the textual labels used in the relationship diagram.

                 

                Cheers, Nick