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

[MKF62]

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.

[George_Thompson]

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‌

[MKF62]

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...

[George_Thompson]

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.

[MKF62]

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...

[George_Thompson]

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?

[MKF62]

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.

[George_Thompson]

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.

[Asrujit_SenGupta]

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.

[feralcatcolonist_old]

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