Creating a weekly FGDB backup of an SDE

[AdamCrateau1]

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 tool, as there are about 25 feature classes that need to be copied.

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?

Thanks,  Adam

[JakeSkinner]

Hi Adam,

Take a look at the Export XML Workspace Document and Import XML Workspace Document tools.

[AdamCrateau1]

Thank you Jake,

Those tools do seem to be the best solution.  In our situation, our entire geodatabase is close to 1 gig of data.  In my testing, the exported XML file is roughly twice that size, or 1.9 GB, which can pose some issues - Catalog hangs on this file when you click on it e.g.   The other option is to iterate through the tables and copy them straight across.  Both options work, and your suggestion is definitely the easiest.

[JoeFlannery]

We use a python script to backup SDE featureclasses to FGDB.

# Use this script to "backup" features from an SDE database
# to an FGDB

# Import arcpy module
import arcpy, os

# Overwrite existing files
arcpy.env.overwriteOutput = True

# Local variables:
in_features1 = "Database Connections\\ABC_ALL_02.sde\\GIS_ABC_ALL.GIS1.ABC_Edit_Lines"
out_features1 = "\\\\SERVER\\ArcGISServer_MapDocs\\Clients\\A-C\\ABC\\ABC_ALL\\Backup.gdb\\ABC_Edit_Lines"

in_features2 = "Database Connections\\ABC_ALL_02.sde\\GIS_ABC_ALL.GIS1.ABC_Edit_Points"
out_features2 = "\\\\SERVER\\ArcGISServer_MapDocs\\Clients\\A-C\\ABC\\ABC_ALL\\Backup.gdb\\ABC_Edit_Points"

in_features3 = "Database Connections\\ABC_ALL_02.sde\\GIS_ABC_ALL.GIS1.ABC_Edit_Polygons"
out_features3 = "\\\\SERVER\\ArcGISServer_MapDocs\\Clients\\A-C\\ABC\\ABC_ALL\\Backup.gdb\\ABC_Edit_Polygons"

# Process: Copy Features
arcpy.CopyFeatures_management(in_features1, out_features1)
arcpy.CopyFeatures_management(in_features2, out_features2)
arcpy.CopyFeatures_management(in_features3, out_features3)

del arcpy, os, in_features1, out_features1, in_features2, out_features2, in_features3, out_features3

[DavidColey]

We copy features as well, and feature class to feature class to handle our spatial views as we found that xml workspace document export had trouble handling them.  It's quite possible that has changed since 10.1.

[847396730]

I use a one-way replica for this.  The synchronize replica script is very simple, and only deltas are communicated.

[AdamCrateau1]

Marianne, is this the basic workflow?

1.  Create One-Way Replica

2.  Scheduled script that synchronizes the child replica with the parent,  e.g.

arcpy.SynchronizeChanges_management(SDE, "BackupReplication", GDB, "FROM_GDB1_TO_2")

[JakeSkinner]

Hi Adam,

You syntax looks correct.  Also, if you wanted to use Python to create a true SQL Server database backup, you can use the pyodbc module to do this.  Take a look at this KB article.  Below is an example:

import pyodbc
connection = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
                            server='InstanceName',
                            database='master',
                            trusted_connection='yes',
                            autocommit=True)
backup = "BACKUP DATABASE [VECTOR] TO DISK = N'VECTOR.bak'"
cursor = connection.cursor().execute(backup)
connection.close()
print 'Backup Completed'

[847396730]

Yes, that is correct.

Here is a script which synchs replicas of different types and creates a timestamped logfile.  The script references an SDE connection file whose credentials are appropriate for the task, so in your case, it would be an OSA connection file.  We store our connection files in a central location, as opposed to on a local user's C drive, which helps with this workflow but is not required.  By default, your connection files are here: C:\Users\username\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog

import arcpy,sys,time
timestr = time.strftime("%Y%b%d-%I%M%S%p")
LogFilePath = '\\\FileServer\\NetworkShare\\ArcSDE\\Replicas\\ReplicaSynchronizationLogs'
final = LogFilePath + "\\" + timestr + "-ReplicaSync.txt"
f = open(final,'w')
ConnectionFilePath = "//FileServer/NetworkShare/ArcSDE/sde_connection_files"

#SDE to File Geodatabase Replica:
try:
    replicaName = "Schema3.Schema3Owner_Features_FileServer"
    print "Synching replica " + replicaName
    f.write("Synching replica " + replicaName + '\n')
    arcpy.SynchronizeChanges_management(ConnectionFilePath +"/Schema3_database_int.sde"
                                        ,replicaName
                                        ,"//FileServer/Share/Geodatabases/data_Schema3Owner_Features.gdb"
                                        ,"FROM_GEODATABASE1_TO_2"
                                        ,"IN_FAVOR_OF_GDB1"
                                        ,"BY_OBJECT"
                                        ,"DO_NOT_RECONCILE")
    print "SUCCESS - " + replicaName
    f.write("SUCCESS - " + replicaName + '\n')
except Exception as e:
    print "ERROR - " + replicaName + ":", str(e)
    f.write("ERROR - " + replicaName + ":" + str(e) + '\n')

    
#One-way SDE to SDE Replica:
    
try:
    replicaName = "data.data_Roads_Read"
    print "Synching replica " + replicaName
    f.write("Synching replica " + replicaName + '\n')
    arcpy.SynchronizeChanges_management(ConnectionFilePath +"/data_database_int.sde"
                                        ,replicaName
                                        ,ConnectionFilePath +"/data_databaseread.sde"
                                        ,"FROM_GEODATABASE1_TO_2"
                                        ,"IN_FAVOR_OF_GDB1"
                                        ,"BY_OBJECT"
                                        ,"DO_NOT_RECONCILE")
    print "SUCCESS - " + replicaName
    f.write("SUCCESS - " + replicaName + '\n')
except Exception as e:
    print "ERROR - " + replicaName + ":", str(e)
    f.write("ERROR - " + replicaName + ":" + str(e) + '\n')
#One and Two-way SDE Replicas with Geodata Service:

try:
    replicaName = "schema1.ReplicaName"
    print "Synching replica " + replicaName
    f.write("Synching replica " + replicaName + '\n')
    arcpy.SynchronizeChanges_management(ConnectionFilePath +"/Schema1owner_database_int.sde"
                                        ,replicaName
                                        ,ConnectionFilePath +"/arcgis on GISServer_port# (publisher)/Schema1owner_database_int.GeoDataServer"
                                        ,"FROM_GEODATABASE1_TO_2"
                                        ,"IN_FAVOR_OF_GDB1"
                                        ,"BY_OBJECT"
                                        ,"DO_NOT_RECONCILE")
    print "SUCCESS - " + replicaName
    f.write("SUCCESS - " + replicaName + '\n')
except Exception as e:
    print "ERROR - " + replicaName + ":", str(e)
    f.write("ERROR - " + replicaName + ":" + str(e) + '\n')

try:
    replicaName = "Schema2.Schema2owner"
    print "Synching replica " + replicaName
    f.write("Synching replica " + replicaName + '\n')
    arcpy.SynchronizeChanges_management(ConnectionFilePath +"/Schema2_database_int.sde"
                                        ,replicaName
                                        ,ConnectionFilePath +"/arcgis on GISServer_port# (publisher)/Schema2_database.GeoDataServer"
                                        ,"FROM_GEODATABASE1_TO_2"
                                        ,"IN_FAVOR_OF_GDB1"
                                        ,"BY_OBJECT"
                                        ,"DO_NOT_RECONCILE")
    print "SUCCESS - " + replicaName
    f.write("SUCCESS - " + replicaName + '\n')
except Exception as e:
    print "ERROR - " + replicaName + ":", str(e)
    f.write("ERROR - " + replicaName + ":" + str(e) + '\n')

[ThomasColson]

You really should have a full SQL backup/recovery plan, of which there is a plethora of examples on the internet. I have approx 30 GB of SDE/SQL data in 14 unique databases for which I run a combination of stored procedures and agent jobs to handle a nightly back up. However, you're on the right track with an off-line copy of the Geodata itself, it could take IT days (weeks, months) to recover your DB server, and having access to operational data is key. In that case, the python tools work well here. In this example, I'm dumping out my entire "base" data SDE Geodatabase into a FGDB, which not only gives me an off-line copy, but I run this nightly as well: any edits posted to the SDE will make their way to network shares and portable hard drive for use by field staff that aren't on the network, and they always have the latest data.

import arcpy, sys, traceback

from arcpy import env

import time, os, sys

from subprocess import call

arcpy.CheckOutExtension("Foundation")

arcpy.CheckOutExtension("Spatial")

arcpy.env.workspace = "X:\\GIS_Final\\data\\basedata\\basemap\\Data\\Working\\Python_Automated_Scripts\\GRSM.sde"

OutFolder = "X:\\GIS_Final\\data\\basedata\\basemap\\Data\\Working"

OutName = "GRSM_"+time.strftime("%Y%m%d%H%M%S")+".gdb"

GDB = OutFolder+"\\"+OutName

if os.path.exists("X:\\GIS_Final\\data\\basedata\\basemap\\Data\\Working\\GRSM.gdb"):

    arcpy.Delete_management("X:\\GIS_Final\\data\\basedata\\basemap\\Data\\Working\\GRSM.gdb")

else:

    print "Nothing to Delete"

arcpy.env.configKeyword = "DEFAULTS"

arcpy.CreateFileGDB_management(OutFolder, OutName)

arcpy.ExtractData_production("GRSM.DBO.Points_Of_Interest",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.Boundaries_And_AOIs",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.Hydrography",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.DisasterManagement",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.Facilties",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.PEPC",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.Air_Photo_Footprints",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.Elevation",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.GridsAndGrats",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.ExtractData_production("GRSM.DBO.Transportation",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.env.workspace = "X:\\GIS_Final\\data\\basedata\\basemap\\Data\\Working\\Python_Automated_Scripts\\CRGIS.sde"

arcpy.ExtractData_production("X:\\GIS_Final\\data\\basedata\\basemap\\Data\\Working\\Python_Automated_Scripts\\CRGIS.sde\CRGIS.DBO.Cemeteries",GDB,"DO_NOT_REUSE","NO_FILTER_BY_GEOMETRY","INTERSECTS","")

arcpy.Rename_management(GDB+"/Cemeteries",GDB+"/Cultural","")

arcpy.Copy_management(GDB, "X:\\GIS_Final\\data\\basedata\\basemap\\Data\\Working\\GRSM.gdb", "")

os.system (r'robocopy X:\GIS_Final\data\basedata\basemap\Data\Working\GRSM.gdb X:\GIS_Final\data\basedata\basemap\Data\Final\GRSM.gdb /MIR /FFT /Z /XA:H /W:5 /R:15  /XF *.lock')