2

Is it possible to use Python to do a backup of ArcSDE databases stored in SQL Server Express? I have been using a custom executable that uses Arcobjects

IDataServerManagerAdmin.BackupGeodatabase() 

but, I would prefer to use Python. The executable makes our IT staff a bit nervous.

1
  • 2
    Please edit the question to clarify whether you have an enterprise geodatabase or a workgroup database in SQL-Server Express. – Vince Sep 9 '15 at 17:47
2

Backup of a geodatabase on a database server, can be done using ArcCatalog and Arcobjects (IDataServerManagerAdmin.BackupGeodatabase()). Unfortunately it is not exposed to python or Arcpy.

So to automate the process of backup you have to create a stored procedure in MS SQL Server and then create a batch file to call the stored procedure. Finally, schedule the batch file.

Stored Procedure

USE [master] 
GO 
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO  

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200) 
AS 

       SET NOCOUNT ON; 

            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name

            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END

            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000) 
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                  

            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL
      BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F' 
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END
       IF @backupType = 'L' 
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END

-- Execute the generated SQL command
       EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

Batch File

  1. for Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

    sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

  2. Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

    sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType=’D’"

And schedule it with windows Task Scheduler.

Reference: How to schedule and automate backups of SQL Server databases in SQL Server Express

1

I agree with @Farid Cher's answer that ideally you should look to accomplish this through a SQL based backup, rather than an externally scripted backup. However, if you are determined to backup the data via python, I can think of 2 possibly solutions. Please note, neither of these are true database backups. However, they can still externally save the data from your database so, in the event of a loss of the database, you could create a new SDE database and copy the backed-up data into it.

Option 1) Create a one way replica from your SDE database out to a FileGDB that would ideally be on another machine at a different site (replicate either using archiving or traditional versioning based replica). Then from python you could simply schedule a python script that uses arcpy to synchronize the replica any time you need. Some of the limitations of this are that it will only back-up one version (whichever version you create the replica off of). Also, any time you change the schema of your database (adding/changing fields and/or adding/changing feature classes/tables) you will need to either synchronize the schema changes and/or re-create the replica - depending on the type of schema change. Additionally, all feature classes you want backed-up will need to be either registered as versioned or have archiving enabled, depending on what type of replica you use.

Option 2) You could write a script (or search the forums and help sites and piece together existing code for it), to get a python script that will loop through every feature class, feature dataset, toolbox, table, etc... in the SDE database and copy the dataset to a FileGDB. Some things to be careful of if using this route: if you have relationship classes, be careful because copying tools in ArcGIS can do some interesting things with related data, so your script will have to make sure all data gets correctly copied over (I have some sample code I can help you with this if you really want to go this route). The limitations of this are, while you can theoretically do this for multiple versions of data, you will have to do a separate backup FileGDB for each edit version, meaning your backup harddrive space requirements and the time required to run the backup can grow large/long quickly. Also, this backup method can take considerably longer than is really necessary for a backup. This also requires re-writing the entire database every time you want to run a backup, so it can be a lot of harddrive disk activity consumption (would need to be scheduled to run at a down time for the server/computer) and would need to definitely not be backed up to a FGDB solid state drive or a flash-drive as this process could relatively quickly max out the write limitations on the disk depending on the frequency and size of the database. Also, this method may (depending on what copy tools you use) preserve GlobalID field values, but is definitely NOT guaranteed to preserve them, so don't use it if preserved globalids are a requirement for you.

Again, I strongly suggest using a SQL based backup to get a true backup of the database using the scheduled batch file method described in another answer, but, if you absolutely have to do something via Python, hopefully the above gives you a few ideas to work with.

2
  • What do you mean with SQL based backup? Do you mean to use the backup utilities of the database itself? – Below the Radar Jul 9 '19 at 18:54
  • 1
    @BelowtheRadar yes, I was referring to the database backup options built into SQL Server. FaridCheraghi's answer on this same question goes more into detail on some of those options. – John Jul 9 '19 at 20:04

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.