Backup of Branch Versioned Data - Is SQL Server backup enough?

813
4
01-27-2022 07:04 AM
KimGarbade
Occasional Contributor III

I'm using Branched Versioning.  I've learned that I cannot write SQL queries in SQL Server to query my base table directly (in hopes of replicating the results of a versioned view), but that didn't stop me from trying.

I did look at the SDE schema tables in SQL Server to see if I could figure out how the REST API could mine records from specific versions/queries.  Granted I didn't look for hours, but it didn't look to me that the SQL Database alone contained the data necessary to make the queries. 

That leads me to believe that the datastore on the enterprise you published your service to is storing additional information about your branch versioned data.  Is backing up my branch versioned database using SQL Server alone enough to ensure data security, or do I need to backup my SQL Database and the Enterprise Datastore?  Any suggestions about best practices would be appreciated.

4 Replies
NickKopczyk
New Contributor II

I also can't seem to locate any best practices for backup and restore workflows in branched versioning.  Any direction to a good resource would be appreciated.

0 Kudos
George_Thompson
Esri Frequent Contributor

Here is a link to using SQL with Branch Versioned data: Utility Network Journey: Branch Versioning and SQL – https://www.esri.com/arcgis-blog/products/utility-network/electric-gas/utility-network-sql/

I would guess that a full SQL backup would be needed to restore any data. The edits are stored in the base table, but there are some other tables that help "track" the versioning; https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/geodatabase-system...

--- George T.
NickKopczyk
New Contributor II

Thanks for the great links George.  I think there is enough here to at least do a test of the process. 


0 Kudos
VeerSingh2
New Contributor III

Here is the SQL query to get the unqiue records from branch versioned Geodatabase table e.g.-PIPELINEDEVICE

SELECT * from [SDE].[PIPELINEDEVICE]
where GDB_ARCHIVE_OID IN (
SELECT GDB_ARCHIVE_OID
FROM (SELECT GDB_ARCHIVE_OID ,ROW_NUMBER() OVER (PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) rn,GDB_IS_DELETE
FROM [SDE].[PIPELINEDEVICE] WHERE ((GDB_BRANCH_ID = 0 AND GDB_FROM_DATE >=
(select [ancestor_moment] from [dbo].[SDE_branches] where branch_id = 0)
))) MB_
WHERE rn = 1 AND GDB_IS_DELETE = 0)

0 Kudos