topic Re: Backup of Branch Versioned Data - Is SQL Server backup enough? in Geodatabase Questions

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

KimberlyGarbade — Thu, 27 Jan 2022 15:04:59 GMT

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.

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

NickKopczyk — Mon, 22 Aug 2022 15:12:15 GMT

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.

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

George_Thompson — Mon, 22 Aug 2022 16:33:28 GMT

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-tables-sqlserver.htm

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

NickKopczyk — Tue, 06 Sep 2022 12:32:55 GMT

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

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

VeerSingh2 — Tue, 29 Nov 2022 19:09:07 GMT

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)