11 Replies Latest reply on Mar 6, 2015 10:30 AM by Matt Coles

    Restore PROD back up to TEST environment as part of upgrade

    Nagarajan R

      Hi Experts.

      We are planning to upgrade our TEST environment to latest version Tableau Server 8.3.2. As part of the upgrade, we wish to restore data from PROD to TEST so it may help through the testing process.

      Has anyone done this before? Following are the challenges i can think of


      1) PROD data sources and workbooks will be pointing to PROD Database. When restoring in TEST, should all this be manually opened in Tableau Desktop and repointed to TEST Database?

      2) Can only the data be restored without configuration setting as there might be changes in configuration setting between TEST & PROD?


      Also as part of the upgrade, Tableau uninstall of existing version creates its own back up and uses that file inside tableau installation path while Tableau new version is installed.

      This would mean that by default the new version installation will pick up TEST back up file.

      If this is the case, is it ok to do a restore of data alone using "tabadmin restore --no config" using PROD .tsbak file?


      Any other challenges please let us know.


      It will help if you can share your responses, experience and suggestions on the above question



        • 1. Re: Restore PROD back up to TEST environment as part of upgrade
          Jessy Cool

          Do yo have seperate server for test and prod ?

          • 2. Re: Restore PROD back up to TEST environment as part of upgrade

            Hey Nagarajan,


            As far as I know, you can complete the restore by swapping out the backup files and restoring --no-config. You will still need to point the workbooks to the new databases however.



            • 3. Re: Restore PROD back up to TEST environment as part of upgrade
              Nagarajan R

              Yes Jessy. TEST & PROD are separate servers and each environments has 2 servers each (Primary and worker configuration)

              • 4. Re: Restore PROD back up to TEST environment as part of upgrade
                Nagarajan R

                Hi Diego, Should this repointing be done manually one by one? If there are 30 data sources and 50 workbooks, should i open each data source and workbook from Tableau Desktop and re-point to TEST?

                Will the Users/Groups also be copied as part of "Restore --no-config" option?



                • 5. Re: Restore PROD back up to TEST environment as part of upgrade

                  I got some clarification and found out:


                  1. The workbooks will be updated automatically to point to the new database once you do the restore--no-config, so long as the restore is of a new installation.


                  2. The users and groups will more over with --no-config but can be removed.

                  • 6. Re: Restore PROD back up to TEST environment as part of upgrade
                    Nagarajan R

                    Hi Diego,


                    I suppose the PROD workbooks/data sources inside Tableau needs to be manually re-pointed to TEST Database and TEST Tableau Server post restore even for new installation.

                    I restored my workbooks/data sources with --no-config option and in TEST, but the workbooks are still pointing to PROD Tableau Server and data sources are pointing to PROD database


                    Also if there are any Tabcmd scripts will those be restored while doing upgrade. Will --no-config restore tabcmd scripts too?




                    • 7. Re: Restore PROD back up to TEST environment as part of upgrade
                      Matt Coles

                      Hi Nagarajan. We do exactly this each time we prepare to upgrade our production Tableau Server instance here at Tableau Software to a new major or minor release. (In fact, we just finished doing it with 9.0!)


                      A few questions and thoughts on what you've asked:


                      • When you say "PROD Database" do you mean one or more database servers that are external to Tableau Server that are being used as sources of data for Tableau Server? Such as a SQL Server instance, or PostgreSQL, or Hadoop, or what have you? If you have TEST versions of these systems, then when you restore a backup of your PROD Tableau Server to your TEST Tableau Server, you will need to bulk edit all of your connections to point to the corresponding TEST servers once the restore is complete by using the Data Connections page under the Admin tab in the Tableau Server web interface. If not all data sources have TEST equivalents, and as a result you're only updating some of them, be sure to document which need to be altered so that you can do it again in the future.

                      • If any of your data sources on the TEST server by necessity still need to point to production database servers, be certain that your testing does not generate enough load to impact them negatively. This load could be generated by extract refresh schedules that come over with the PROD backup, or if you have automated load testing scripts.

                      • Do you use your TEST server to test subscriptions? If you do not wish for everyone to receive their subscriptions from the TEST Tableau Server instance as well as the PROD instance, you will need to disable those schedules each time you do a restore.

                      • Make sure that you have a process by which configuration changes made to TEST get made in PROD (assuming you deem them safe). Point is to keep the environments as similar as possible so your tests are valid.

                      • If you use data extracts and refresh them on a schedule, be sure to compare the TEST results to the PROD results once they've run. Monitoring extract refresh history is one of the more important tests for us in our environment.

                      • While it may be tempting to grant wider access to the TEST server instance for testing purposes, once you do the restore, realize that all the same PROD data will be there, so doing so may result in more access to that production data. If you have highly secure data extracts, and foresee the need to have others gain greater access to the TEST server, it may even be worth removing the sensitive content altogether once it has been restored to TEST to avoid that problem.

                      • If you haven't already done so, make sure that any tabcmd / tabadmin scripts you run in PROD have been moved to the TEST server, and carefully reviewed before they are run so that server names and the like are pointed to TEST--you want to test those too, but you definitely don't want them running against PROD! The same goes for any other integrations you might have with third party or in-house tools...ideally you would be testing all of these against TEST as well.


                      I hope all that helps. Let us know if you have more questions.



                      • 8. Re: Restore PROD back up to TEST environment as part of upgrade
                        Nagarajan R

                        Hi Matthew...Really appreciate your detailed response and my apologies for this dealy in saying thank you...

                        To answer your questions

                        1) Yes I meant database (Teradata in my case) that are external to Tableau Server. For all data connections with Connection type as "Teradata" (and NOT "Tableau Server) under Admin --> Data Connections tab, I did a edit to repoint from Prod Teradata to TEST Teradata. It worked fine)

                        2) After the upgrade, I had the necessity to have my Tableau Data sources still point to PROD Teradata for testing purpose. After data testing is done, i repointed them to TEST Teradata. Ensure load did not affect Production

                        3) No Subscriptions at this point

                        4) No configuration changes are encouraged as part of this upgrade. Its like apple to apple testing between TEST and PROD post upgrade

                        5) Extract refresh runs on schedule and this functionality is tested post upgrade and works fine

                        6) User/groups have similar setup in our TEST and PROD Tableau Servers

                        7) Tabcmd scipts are tested with respect to environements and produce the expected functionality.

                        However, I have an issue. When I open Workbooks in Server 8.3.2 from Tableau Desktop 8.3.2, I get an error saying

                        "This workbook contains a Data Source that is dependent on a different Tableau Server.  Multiple Tableau Server connections are not supported."

                        I believe this is because my workbooks in TEST Tableau Server with connection type as "Tableau Server" is pointing PROD Tableau Server.

                        How do i change this from PROD to TEST Tableau Server?

                        Please Help!!!

                        • 9. Re: Restore PROD back up to TEST environment as part of upgrade
                          Matt Coles

                          Nagarajan, I'm not sure how your workbooks got into that state. In our environment, where we've restored a backup to our other server, I do not encounter the same issue--all workbooks on that "TEST" server that I use Desktop to connect to reference the proper datasources from the local server--it seems that they automatically update themselves to refer to whatever server they're located on.


                          So, here's a theory: I wonder if the gateway.public.host value in [install root]\Tableau Server\data\tabsvc\config\workgroup.yml is set to the same value as your PROD server? For example, if in that file you saw the following for your two environments:



                          gateway.public.host: myprodserver



                          gateway.public.host: myprodserver


                          If that's what you see, I recommend switching the TEST value for that entry to the common name for your TEST server, e.g., "mytestserver", then restore the backup from PROD again. I think that is the value being used to determine what connections workbooks would use in the restore process (we set that value for other reasons, but our server backups have always "just worked" with regard to the published data sources.

                          • 10. Re: Restore PROD back up to TEST environment as part of upgrade
                            Nagarajan R

                            Hi Matthew,

                            I am not sure either but i was thinking it would have been more easy if the tool had an edit option on twb workbooks on server with connection type as "Tableau Server". If you have observed they have this simple edit option enabled (using this you may repoint database from say for ex from PROD DB to TEST DB) for data sources with connection type as "Database Name" (in my case "Teradata"). You can check this under Admin --> Data Connections tab in the tableau server web.


                            But more of a general question about migrating workbooks from TEST to PROD Tableau Server. Assume

                            1) there are data sources (as Tableau extracts (not live connection) - extract data from Teradata connection) published to TEST Tableau Server

                            2) you create a workbook (with say 4 worksheets) by using a copy of Tableau Server data sources (lets say we use 4 data sources in this workbook)

                            3) You then publish this workbook to TEST Tableau Server


                            Now if you have to migrate this to PROD Tableau Server, you need to change connection of tableau server data sources  copy inside this workbook to point to PROD Tableau Server data sources.

                            Whats the general procedure to do?


                            I am trying to find out if its problem with the data restore I did from PROD to TEST.


                            And as an information I am able to successfully edit connections and open all .twbx type workbooks published to server from Tableau Desktop (post upgrade) . The issue I have is only with .twb type workbooks that uses a particular server data source copy.


                            Please let me know if my queries are not clear. i can explain further. More often we write our queries with our understanding and what we have seen but miss to help understand the audience of our issue by being precise and clear. This is important because the audience/participants trying to help us resolve our issues would not have seen what we have.




                            • 11. Re: Restore PROD back up to TEST environment as part of upgrade
                              Matt Coles

                              If you used tabadmin backup / tabadmin restore --no-config to move the PROD content to TEST, your workbooks should all reference the TEST published datasources, not the PROD ones. So I'm still confused as to why your workbooks are in that state (I still wonder about the config value setting from my earlier reply).


                              My own personal (unsupported) technique for pointing .twbs at a new Tableau Server is to open them up in a text editor, and find/replace with the new Tableau Server name. It works, but it can be a bit tedious if you have to do it on a lot of workbooks. Another alternative might be the Enterprise Deployment Tool that Interworks created to support just these kinds of scenarios. I have used it a few times, and it definitely beats manually downloading and altering them all manually.


                              I highly recommend figuring out why the restore did not point the workbooks at the TEST datasources--it is likely the path of least frustration, time, and cost.