[GIS] Creating a weekly FGDB backup of ArcSDE

enterprise-geodatabasefile-geodatabasepythonsql server

I need to create a script that will copy our county's parcel data, maintained in SDE/SQL, to a file geodatabase. This script will be run as a scheduled task over the weekend. What GP tool do you recommend for this process? Consider that our schema is 'set in stone' and we have no relationship classes to maintain. I have tested both CopyFeatures_management and FeatureClassToFeatureClass_conversion. Both work. FC to FC is slightly faster, but speed is not really an issue. In theory, CopyFeatures, run with Overwrite = True, matches our workflow as we are not converting to a shapefile or coverage. FeatureClassToGeodatabase_conversion may be the best choice, as there are about 25 feature classes that need to be copied to the same FGDB.

Also, I need to ensure that the machine running the script is able to connect to the SDE data. We are using DBO-schema with OS authentication. The script will likely be run by the same server that houses our SDE. Does anyone have experience with a similar scenario?

Best Answer

I have used this script in the past (Warning, does not copy any topologies, relationship classes, etc):

import arcpy, os

def ExportSDEtoGDB(sde_gdb, out_loc, out_name, gdb_type='File Geodatabase'):
    '''
            Creates a copy of an SDE Geodatabase to either a File or Personal Geodatabase. This
              will copy all tables, rasters, and feature datasets.  All Feature Classes will remain
              inside their respected Feature Datasets in the new geodatabase.

            Parameters:

        sde_gdb: SDE Database
        out_loc: output folder for new geodatabase
        out_name: Name of new geodatabase
        gdb_type: Type of geodatabase.  Default is 'File Geodatabase'

    '''

    # Create GDB
    if gdb_type == 'File Geodatabase':
        gdb = str(arcpy.CreateFileGDB_management(out_loc, out_name, 'CURRENT').getOutput(0))
    else:
        gdb = str(arcpy.CreatePersonalGDB_management(out_loc, out_name, 'CURRENT').getOutput(0))


    # loop thru sde and copy 
    arcpy.env.workspace = sde_gdb
    for table in arcpy.ListTables():
        t_name = table.split('.')[-1]
        arcpy.TableToGeodatabase_conversion(table, gdb)
    for raster in arcpy.ListRasters():
        arcpy.CopyRaster_management(raster, os.path.join(gdb, raster))
    for featd in arcpy.ListDatasets('*','Feature'):
        arcpy.env.workspace = fd = os.path.join(sde_gdb, featd)
        fd_name = featd.split('.')[-1]
        sr = arcpy.Describe(fd).spatialReference
        gdb_fd = str(arcpy.CreateFeatureDataset_management(gdb, fd_name, sr).getOutput(0))
        arcpy.AddMessage('Created Feature Dataset: %s' %fd_name)
        for fc in arcpy.ListFeatureClasses():
            fc_name = fc.split('.')[-1]
            arcpy.FeatureClassToFeatureClass_conversion(fc, gdb_fd, fc_name)
        arcpy.AddMessage('Copied all feature classes from: %s\n\n' %featd)
Related Question