topic Re: Copy production Database and "rename" as test database - what is the proper workflow? in Data Management Questions

Copy production Database and "rename" as test database - what is the proper workflow?

MKF62 — Fri, 29 Sep 2017 11:44:36 GMT

I have a database in production and need to update an old copy of it, the "test" database, where I can only assume the person in this position before me did a backup & restore to create the test database, but renamed it during the restore process thus I cannot actually connect to it in ArcMap because all the SDE tables have the production database name, not whatever he renamed it to (cause renaming is a no-no with SDE geodatabases). I can't revert this test database back to the original name because the production database exists in the same SQL Server instance and you can't have two databases named the same. Plus, the data in it is old and I want to update it.

So, what is the proper workflow for creating a copy of a database with a new name? I'm thinking it'd look something like this:

1) Use Create Enterprise Geodatabase tool with the new name "MyDatabaseTest"

2) Add users (is there any way to copy users from the production database to the new test one or do you really have to go through and do it manually again?)

3) Use ArcCatalog to copy/paste feature classes and tables from production database to new test database

How does this copy/paste process affect existing table relationships in the RDMS? I worry about losing all of that.

Will this workflow work? I assume after going through this, I would be able to use direct connection in ArcMap to connect to the new test database, as that is really the goal.

For reference, working with SQL Server 2014 and Desktop 10.5. I'm not sure what version of SDE we're working with, if it's the same as Server it'd be 10.4.1.

Re: Copy production Database and "rename" as test database - what is the proper workflow?

George_Thompson — Fri, 29 Sep 2017 11:57:03 GMT

As you found out you cannot change the name of the SQL Server database once created. This is also true if you have a second instance of SQL Server, the DB name must stay the same always.

No matter which of the methods you use to move your SQL Server database, you cannot rename the database. When you restore a database, for example, you are given the opportunity to restore it with a different name. Don't do this with a geodatabase; you won't be able to connect to it.

Here is the documentation on the ways to move SQL Server DB's: Methods to move a geodatabase in SQL Server—Help | ArcGIS Desktop

Copy/Paste into a new SQL Server EGDB should not impact the relationships and carry them over. If the data is versioned, it will only copy over the data for the version in which you are connected.

Hope this helps!

Geodatabase‌ ArcGIS Enterprise‌

Re: Copy production Database and "rename" as test database - what is the proper workflow?

Asrujit_SenGupta — Fri, 29 Sep 2017 12:41:01 GMT

Consider the possibility of having a separate Testing\Development SQL Server instance. That way it would be easier to have updated data on the Testing database, through simple backup\restore whenever needed.

Re: Copy production Database and "rename" as test database - what is the proper workflow?

MKF62 — Fri, 29 Sep 2017 13:38:19 GMT

I just followed through with my outlined steps above and after copying the data over, it does not appear the relationships carried as you stated they would. How are you getting the table relationships to carry over? When I look at dependencies for a particular table that I know should have many relationships in SQL Server Management Studio, there are none...

Re: Copy production Database and "rename" as test database - what is the proper workflow?

George_Thompson — Fri, 29 Sep 2017 13:42:19 GMT

You will not find the relationships in SSMS or the root EGDB in SQL Server. These are objects that ONLY exist in the Geodatabase and in the ArcGIS Client.

Look at this help: Copying feature datasets, classes, and tables to another geodatabase—ArcGIS Help | ArcGIS Desktop it talks about relationship classes.

Re: Copy production Database and "rename" as test database - what is the proper workflow?

MKF62 — Fri, 29 Sep 2017 13:57:14 GMT

Well then can you maybe help me understand what the person in this position before me did because I can see the relationships for the MonitoringPoint table in the SSMS just fine in the production database. In the image below of (in the production database), the MonitoringPoint table contains point features, so it is spatial. Count and CoveyCount are not spatial. Did the person create these tables in SQL Server and not ArcCatalog and that's why they're showing up in the dependencies for MonitoringPoint in the production database?

When creating the test database I copied/pasted everything using ArcCatalog. Should I be copying/pasting only the spatial layers (feature classes) using ArcCatalog and copying/pasting the rest of the SQL-made tables using SSMS? I want to be able to make sure the relationships are preserved and viewable...

Re: Copy production Database and "rename" as test database - what is the proper workflow?

George_Thompson — Fri, 29 Sep 2017 14:03:50 GMT

I am not sure on the SQL Server side what is created.

What happens if you just copy over the feature class with the relationships to the new EGDB?

Re: Copy production Database and "rename" as test database - what is the proper workflow?

MKF62 — Fri, 29 Sep 2017 14:18:52 GMT

I can see in the GDB_ITEMRELATIONSHIPS table that some relationships are being carried over though I cannot tell what ones given it's just a list of various IDs. I'm assuming this is what you were referring to in your previous post about these relationships only existing in the geodatabase.

Thing is, I can't tell what these relationships actually are considering it's just a listing of various ID strings. This table has no meaning to me.

If I just copy over the feature classes in ArcCatalog and leave the SQL-derived tables, I just get a feature classes in the database, nothing else comes with it (aside from two rows in the GDB_ITEMRELATIONSHIPS table). I think the problem could be that the table relationships were actually originally established in SQL Server, not in ArcMap/Catalog.

Re: Copy production Database and "rename" as test database - what is the proper workflow?

George_Thompson — Fri, 29 Sep 2017 14:22:32 GMT

As for the GDB_Items table (or any GDB/SDE_ table for that matter) is a system and should not make sense, for most people

You may be correct that some of the relationships were created in SQL Server and not ArcGIS. If that is the case, then the ArcGIS client would not know of them and you would be required to transfer them via SSMS.

Re: Copy production Database and "rename" as test database - what is the proper workflow?

feralcatcolonist_old — Tue, 28 Dec 2021 21:04:42 GMT

Not to dig up the dead or anything, but I'm interested in how far down this rabbit hole you went. I'm just discovering these pitfalls as of today RE: inability to restore an enterprise geodatabase to a different name

Re: Copy production Database and "rename" as test database - what is the proper workflow?

HopNguyen — Mon, 29 Jan 2024 20:21:15 GMT

Found this thread and was able to successfully clone a 10.9.1 SDE SQL server database. In my case, wanted to copy AGIS to AGIS10. In SSMS, issue these DMLs:

UPDATE [AGIS10].[dbo].SDE_column_registry SET [database_name] = 'AGIS10'; UPDATE [AGIS10].[dbo].SDE_geometry_columns SET f_table_catalog = 'AGIS10'; UPDATE [AGIS10].[dbo].SDE_layers SET [database_name] = 'AGIS10'; UPDATE [AGIS10].[dbo].SDE_table_registry SET [database_name] = 'AGIS10'; UPDATE [AGIS10].[dbo].[GDB_ITEMS] SET [name] = replace([name],'AGIS.','AGIS10.'), [physicalname] = replace([physicalname],'AGIS.','AGIS10.'), [path] = replace([Path],'AGIS.','AGIS10.')

Re: Copy production Database and "rename" as test database - what is the proper workflow?

George_Thompson — Mon, 29 Jan 2024 21:15:28 GMT

I wanted to post that there is a supported way to do this now with SQL Server Enterprise Geodatabases: https://www.esri.com/arcgis-blog/products/arcgis-pro/data-management/restoring-sql-server-databases-with-a-new-name/

I would not recommend any other way as there may be issues down the road.

Re: Copy production Database and "rename" as test database - what is the proper workflow?

George_Thompson — Mon, 29 Jan 2024 21:15:55 GMT

See my post below, I would not recommend this as it could have impacts down the road that would be unsupported.

Re: Copy production Database and "rename" as test database - what is the proper workflow?

HopNguyen — Mon, 29 Jan 2024 21:20:31 GMT

The reason this is the chosen path is because ArcMap is going away, and for legacy users to transition to ArcGIS Pro. In our instance, ArcGIS Pro and ArcGIS Enterprise will use the none copied SDE DB, we'll be upgrading that to SDE version 11.2. As long as ArcMap users can access their data, that's all we're concern with, it will eventually go away. I do agree, I would not do this for our 11.2 SDE DB.