Data Migration and upgrading geodatabase with SQL Server

02-10-2021 12:24 AM
Labels (1)
Esri Contributor
5 0 262

The workflow for upgrading geodatabase and migrating data using SQL Server backup file.  

The requirement is for Geodatabase 10.3 upgrades to Geodatabase 10.9.x

  1. Source database cut off (no more editing on the production geodatabase)
  2. All GIS users checked in their version data to the default base version.
  3. Do a Fully compress a geodatabase which has enable the versioning.
    The compress command to reconcile and post all outstanding changes in the child version to the DEFAULT version (eg. I*, a*, d* dataset)
  4. DBA to create the Geodatabase full Backup file (*.bak)
  5. Create the relevant SQL user accounts in the new SQL Server 2019 if not exists
  6. Restore the backup file to New SQL Server 2019 with its identical name
  7. Pre-process for Geodatabase Upgrade  
    • Run the premigration SQL script to enable Allow_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT options to be ON (the options are required after ArcGIS geodatabase 10.6+. )
ALTER DATABASE Geodatabase_name

ALTER DATABASE Geodatabase_name
  • Run SQL script to link the user account with current SQL server 2019 (ensure the source geodatabase’s user account is created in step 5. This step to link the restored database with current SQL server instance. )




EXEC sp_change_users_login 'Update_One', 'sde', 'sde'


EXEC sp_change_users_login 'Update_One', 'GeoDatabaseCreator', 'GeoDatabaseCreator'




Execute geodatabase upgrade using ArcGIS Pro.

    • Create database connection file to destination geodatabase with the sysadmin right (not geodatabase admin user)
    • Right-click on the database connection file
    • Expand the Connections section
    • Expand the Upgrade Status
    • Click Run Upgrade
    • Checked options for Perform Pre-Requisite Check and Upgrade Geodatabase

Review the dataset in the new geodatabase.

The above workflow for geospatial data types includes the feature class, tables, dataset, and relationship class/tables. If there is the requirement to upgrade raster_storage or geometry_storage for SDEBINARY will need to add extra steps in the pre-process for Geodatabase upgrade.