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.
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
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'"
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
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.
