5

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

Improve this question
  • 3
    Please edit the question to specify whether you are changing the database name, and more details on the way you're transferring the data. – Vince Sep 9 '16 at 16:06
  • Vince , DBA backup and restore. db name will remain the same – Brad Sep 9 '16 at 17:11
3

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.

Improve this answer
  • The DBA's will be doing this so I guess I need to make sure they sync the users also. They will doing a DB backup and restore – Brad Sep 9 '16 at 17:07
4

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).
Improve this answer
  • We need to avoid creating an "empty target geodatabase". We want to copy all of the sde tables also. – Brad Sep 9 '16 at 17:05
  • You can restore your sde schema to the new 'empty' database, thereby copying the sde tables. When we do the restore we carry everything (state tree, user versions, archives, etc.). – Radar Sep 9 '16 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.