Copy Enterprise Geodatabase to new SQL Server - will it work?

Question

ArcGIS Desktop 10.4.1, ArcGIS Server 10.4.1, SQL Server Enterprise GDB

We need to make an exact copy of an Enterprise GDB (SQL Server) and move it to a new server. Besides changing connection strings for the clients, is there anything that will need to be done? All of the SDE functions, stored procs and tables will be moved also.

The ONLY thing that will change is the server name. DBA will do a backup and restore it to the new server. I am expecting it to work without any administrative tasks...

Answer 1

Yes, you can take a backup of an SDE geodatabase and move it to a new server, but it's a bit tricky. We regularly do this for SDE geodatabases with PostgreSQL. The biggest caveat is you cannot rename the database.

The basic steps:

1. Create a consistent database level dump (e.g. SQL file)
2. Create the common roles on the new server (e.g. sde, dataowner, dataeditor...)
3. Create database specific (to the database being restored) roles. I would recommend generating this list before you nuke the existing database.
4. It may be necessary to create non-database specific roles in order to avoid error messages during the restore. Privileges are revoked from roles defined in the source database instance during a database restore. In order to avoid errors the target database instance should have roles matching the source database instance.
5. Create an empty target geodatabase
6. Create the sde schema and grant usage on it
7. Create the dataowner and additional schemas
8. Restore the public and sde schemas from your backup
9. Restore the dataowner schema(s) from your backup. This takes more time if you use multiple dataowners (e.g. individual users).
10. Restore any additional schemas from your backup
11. Perform post-restore maintenance operations using the built-in Esri tools(rebuild system tables and dataset indexes, calculate geodatabase statistics).

Answer 2

Something that might trip you up is the database user accounts. Depending on how you move the data, I always use a full DB backup and restore, the DB users in the restored DB won't have logins in the new instance, or the internal IDs will not match. Lucky you can also sync users. The scripts in the linked article show you how to import users from one instance to another.