Creating a weekly FGDB backup of ArcSDE?

Question

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?

Answer 1

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)

Answer 2

You could consider exporting to an XML workspace document via the Export XML Workspace Document GP tool.

You can input a geodatabase and then all objects including any behavioural ones will get exported. The problem with Copy Features and FC to FC is that they leave the behavioural objects behind (i.e. networks, topology, etc.). May not be relevant for you at this point but still worth mentioning for future reference. In addition, it is one tool for the entire geodatabase instead of looping through all your FCs one at a time.

As for running the script as long as the user account used has the appropriate privileges to read the data then you are good to go (i.e. the account does not necessarily have to be part of DBO). In your script, best to just point to your connection file.