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.

[HopNguyen]

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

[George_Thompson]

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

[HopNguyen]

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.

[George_Thompson]

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

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