11 Replies Latest reply on Apr 16, 2013 11:09 AM by mboeringa2010

    Copy ArcSDE Enterprise Geodatabase

    ldonahue
      Is it possible to make a copy of an enterprise ArcSDE 10.0 geodatabase on SQL Server 2008 R2 and move it to another instance of SQL Server 2008 R2 and rename the copy to some other name?  Using only SQL - no Arc products?
        • Re: Copy ArcSDE Enterprise Geodatabase
          vangelo-esristaff
          No.  The "rename" part is where the process would fail.

          - V
          • Re: Copy ArcSDE Enterprise Geodatabase
            ldonahue
            That is what I thought.

            So if there are some people trying to hack the sde geodatabase by replacing all occurrences of the database name with a new name (simply by doing a find/replace in the triggers,tables, etc), that will still not work?

            I'm about to feel somewhat vindicated here, but will hold off on my joy a few more moments...
            • Re: Copy ArcSDE Enterprise Geodatabase
              vangelo-esristaff
              While it's possible, rewriting the majority of the triggers in a database is not
              a supportable activity.  I certainly wouldn't attempt it, not when you can use
              a named instance with the existing database name.

              - V
              • Re: Copy ArcSDE Enterprise Geodatabase
                ldonahue
                Well, they did it anyway. 

                They didn't like the database name and wanted it named:  databasenamedev

                so it is going to work for them... ok... joy has left.
                • Re: Copy ArcSDE Enterprise Geodatabase
                  vangelo-esristaff
                  Cheer up -- it will likely fail in some inexplicable way down the road (or maybe
                  sooner, it they didn't take XML update into account ;)

                  In my book, wanting to change the database name is equivalent to wanting
                  to start database setup all over again.  Doing anything else is asking for
                  system failure.

                  - V
                  • Re: Copy ArcSDE Enterprise Geodatabase
                    ldonahue
                    I offered to install another sde instance for them, with a dev database name.  They said they didn't need my help, so it's out of my hands.  I wonder if this application they are trying to replicate in a development environment uses app server connections... hmm..
                    • Re: Copy ArcSDE Enterprise Geodatabase
                      ldonahue
                      Hold on a second.

                      Even if the client makes a direct connection to this copy, without ArcSDE being installed, what is the point of the "service" property using:  sde:sqlserver:databasename

                      Doesn't that still require ArcSDE 10.0 to be installed in order to use that?
                      • Re: Copy ArcSDE Enterprise Geodatabase
                        vangelo-esristaff
                        Both flavors of access to the geodatabase require the same things to establish
                        connection.  If the metadata in the SDE (or DBO) user tables no longer point
                        to tables, then there will be trouble, but no, ArcSDE (the software) isn't required
                        to be installed, because Direct Connect *is* an ArcSDE server.

                        - V
                        • Re: Copy ArcSDE Enterprise Geodatabase
                          ldonahue
                          Connecting to this "backup, rename and restore" copy of SDE, via direct connect and without installing SDE on this SQL Server, fails.
                          <insert>  I KNEW IT!!  I TOLD THEM IT WOULDN'T WORK!!   Muahhaaahaaaaa!   lol.  </insert>

                          Now that this is out of my system...

                          The error message I got in ArcCatalog making a direct connection to this "backup, rename, and restore" copy of SDE.

                          Failed to connect to the specified server.
                          This release of the GeoDatabase is either invalid or out of date.
                          DBMS table not found [Microsoft SQL Server Native Client 10.0: Invalid object name 'databasename.sde.GDB_Release'.][databasename.sde.GDB_Release]


                          Client and server are both at 10.0, the production geodatabase was not upgraded, I checked.

                          I can not say whether this is related to not having ArcSDE installed on this instance of SQL Server or whether the sde login needs to be resynced after the backup and restore. 

                          I can say that the people administering this SQL Server are pointing to ESRI as the problem because they cannot log into the renamed geodatabase using the logins/passwords that the original production sde geodatabase had and they think that ESRI security is causing the problem.  I don't even know how to respond to that.
                          • Re: Copy ArcSDE Enterprise Geodatabase
                            vangelo-esristaff
                            Even after rewriting all the triggers, there's still two possible trip points:
                            Microsoft's security model (users, schemas, & logins and their ids)
                            The contents of the SDE.sde_* and SDE.gdb_* tables.

                            Renaming databases remains very unsupported.

                            - V
                            • Re: Copy ArcSDE Enterprise Geodatabase
                              mboeringa2010
                              I can say that the people administering this SQL Server are pointing to ESRI as the problem because they cannot log into the renamed geodatabase using the logins/passwords that the original production sde geodatabase had and they think that ESRI security is causing the problem.  I don't even know how to respond to that.


                              ESRI geodatabase security = RDBMS security = (most likely in case of SQL Server) Windows Authentication

                              So if you can't login after recreating your database and nothing else changed, you've taken the wrong path (as Vince already told you: you can't rename the database with an established geodatabase in it).

                              The path you probably should take if you really need to "rename" (note the quotes):

                              1) - Create a new database using the new name in your instance (or if desired an entire new instance)
                              2) - Re-create any database users / roles etc. of the old database you wished to "rename" in the new database
                              3) - Use the "Enable enterprise geodatabase" tool to create a new ArcSDE Repository in the new geodatabase (Alternatively you could have used the "Create enterprise geodatabase" tool to do steps 1 and 3)
                              4) - Copy over any datasets (Feature Datasets / Feature Classes) from the old geodatabase to the new one, or use One-Way Geodatabase Replication to do it. There may be some headaches here, especially if there are outstanding versions and editing is going on. See also Understanding distributed data

                              Vince can probably give you more details and correct/supplement what I wrote.