Archive Restore Tool

[KevinDunlop]

So, you been using geodatabase archiving to keep a history of your data's changes.  But you need to make a change to the feature class that requires archiving/versioning to be turned off or worst, you need to change a different feature class in the same data set.  You turn off, archive, make the change, and go to turn it back on.  Well poopy, your old archive history can't be reconnected and it is in a separate feature class.  And there is no tool to restore it.

That was the issue I was facing with several of my feature classes.  In addition, with a near future database vendor switch, I was looking at losing all my archive history.  So I wrote a this tool, the Archive Restore, to put the records in the old archive history back into the current archive history.  

Both the old archive feature class and parent feature class must resided in the same SDE database under the same data owner. Versioning and archiving on the parent feature class must be enabled for this tool to work. Old archive records with no end (current features at the time archiving was turned off), will receive the end date of when the archive was turned back on.

This tool allows for the addition and removal of fields. In the case of where a field is removed from the parent feature class, the field data from the old archive will not be copied over. In the case of an addition, the new field will be left null.

The old archive feature class and records inside it are not deleted during this process.  It must be manually deleted after the tool has finished.  This way you can keep it if you want.

This tool was written in python using Arcpy for ArcGIS 10.4.1.  I turned it into a ArcToolbox script so people who have limited python experience can still use it.  It has been tested against Oracle and Postgres.  I hope to test it against SQL Server in the near future.  If you use any other databases, feel free to provide any changes you need to make them work.

I strongly suggest testing this on some test data before running on your live production database.  While I have tested in our environment, I can't guarantee that it will work in every environment.  Once the old archive records are added to the new archive, they can't be removed without using SQL on the back end.  Use at your own risk.

Please let me know how it worked for you and your data.  I hope it helps.

[Chad__Admin_Quinn]

Do you have a time-frame to create a method/model to use this tool with MSSQL server based enterprise geodatabases?

[KevinDunlop]

I am currently working on setting up the SQL Server database now and will be testing the tool and making the changes within the month.  I hope to have an update by end of July 2017 at the lastest if any changes need to be made.

[Chad__Admin_Quinn]

Do you have an update on whether this tool will be available for MSSQL server architecture?

[KevinDunlop]

Yes and No. 

For the No part, the python script doesn't work because the database function to get the next ObjectID can't work in bulk with SQL Server via the ArcSDESQLExecute method (see Calling sde.next_rowid with ArcSDESQLExecute ).  So I was never able to get it to work.  If anyone can help me solve that issue, I would be glad to make the python work with SQL Server.

For the Yes part, I was able to create the process via an SQL script that can be run directly in SQL Server.  Here is a copy of the SQL code.

--Script to update the archive table in SQL Server.
--This example uses a feature class called LUPAPWC with a schema owner VECTOR
--You will need to update the fields for your feature class

USE GISTEST;
GO

IF NOT EXISTS (select * from sysobjects where name='ArcCutDate' and xtype='U')
     CREATE TABLE VECTOR.ArcCutDate(
     cutDate datetime NULL,
     tableName nchar(50) NULL
     )
GO

SELECT count(*) from VECTOR.LUPAPWC_H; --Current Archive table
SELECT count(*) from VECTOR.LUPAPWC_OLD; --Old Archive Table

SELECT  MIN( gdb_from_date) FROM GISTEST.VECTOR.LUPAPWC_H;
DECLARE @cutDate datetime
SELECT @cutDate =  MIN( gdb_from_date) FROM GISTEST.VECTOR.LUPAPWC_H;
INSERT INTO VECTOR.ArcCutDate(cutDate,TableName) VALUES (@cutDate, 'LUPAPWC');
GO


BEGIN TRAN update_LUPAPWC;
     DECLARE @cutDate datetime
     SELECT @cutDate = cutdate FROM GISTEST.VECTOR.ArcCutDate WHERE TableName = 'LUPAPWC';

     DECLARE @iterator INT
     SELECT @iterator = MIN(OBJECTID_1) FROM GISTEST.VECTOR.LUPAPWC_old WHERE GDB_TO_DATE <= @cutDate
     
     WHILE @iterator is NOT NULL
          BEGIN
               DECLARE @newid int
               DECLARE @gid uniqueidentifier
               EXEC GISTEST.sde.next_rowid 'VECTOR', 'LUPAPWC_H', @newid OUTPUT
               EXEC GISTEST.sde.next_globalid @gid OUTPUT
               
               INSERT INTO VECTOR.LUPAPWC_H (OBJECTID,
                                                       LUPA,
                                                       UPDATE_DATE,
                                                       GLOBALID,
                                                       SHAPE,
                                                       GDB_FROM_DATE,
                                                       GDB_TO_DATE,
                                                       GDB_ARCHIVE_OID) 
                    SELECT OBJECTID,
                              LUPA,
                              UPDATE_DATE,
                            @gid,
                            SHAPE,
                            GDB_FROM_DATE, 
                            GDB_TO_DATE, 
                            @newid
                    FROM VECTOR.LUPAPWC_old 
                    WHERE @iterator = OBJECTID_1;

               SELECT @iterator =  MIN(OBJECTID_1) 
               FROM GISTEST.VECTOR.LUPAPWC_OLD 
               WHERE GDB_TO_DATE <= @cutDate AND @iterator < OBJECTID_1;
          END
     GO
     
     DECLARE @cutDate datetime
     SELECT @cutDate = min(cutdate) FROM GISTEST.VECTOR.ArcCutDate WHERE TableName = 'LUPAPWC';
     
     DECLARE @iterator INT
     SELECT @iterator = MIN(OBJECTID_1) FROM GISTEST.VECTOR.LUPAPWC_old WHERE GDB_TO_DATE > @cutDate
     WHILE @iterator is NOT NULL
          BEGIN
               DECLARE @newid int
               DECLARE @gid uniqueidentifier
               EXEC GISTEST.sde.next_rowid 'VECTOR', 'LUPAPWC_H', @newid OUTPUT
               EXEC GISTEST.sde.next_globalid @gid OUTPUT
    
               INSERT INTO GISTEST.VECTOR.LUPAPWC_H (OBJECTID,
                                                                 LUPA,
                                                                 UPDATE_DATE,
                                                                 GLOBALID,
                                                                 SHAPE,
                                                                 GDB_FROM_DATE,
                                                                 GDB_TO_DATE,
                                                                 GDB_ARCHIVE_OID)
                    SELECT OBJECTID,
                            LUPA,
                              UPDATE_DATE,
                            @gid,
                            SHAPE,
                            GDB_FROM_DATE,
                            @cutDate,
                            @newid
                    FROM GISTEST.VECTOR.LUPAPWC_OLD
                    WHERE  @iterator = OBJECTID_1;

               SELECT @iterator =  MIN(OBJECTID_1) 
               FROM GISTEST.VECTOR.LUPAPWC_OLD 
               WHERE GDB_TO_DATE > @cutDate AND @iterator < OBJECTID_1;
          END
     GO
     select * from GISTEST.VECTOR.LUPAPWC_H; 

--rollback TRAN update_LUPAPWC;
commit TRAN update_LUPAPWC;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Anonymous User]

Hi, Kevin

We have some issues with the SQL script in MSSQL 2016. I'm trying to change feature classes names according to our database but I can't figure out what is the field named "LUPA". Could you what does it refer to?

[KevinDunlop]

LUPA is just an attribute in the feature class.  

Basically change the old to include all your attributes for the table.

OBJECTID,
Attribute1,
Attribute2,
...
AttributeN,
GLOBALID,
SHAPE,
GDB_FROM_DATE,
GDB_TO_DATE,
GDB_ARCHIVE_OID

[Anonymous User]

Thank you for your explanation! I figured this out in my own after writing here . I'm struggling with another problem with my changed script. 

These rows I understand they are for new and old archive tables:

SELECT count(*) from VECTOR.LUPAPWC_H; --Current Archive table
SELECT count(*) from VECTOR.LUPAPWC_OLD; --Old Archive Table

But could  you explain what does "LUPAPWC" refer to? Is it default version feature class

INSERT INTO VECTOR.ArcCutDate(cutDate,TableName) VALUES (@cutDate, 'LUPAPWC');

Example:

SELECT count(*) from dbo.ALA_H1; --Current Archive table
SELECT count(*) from dbo.ALA_H; --Old Archive Table

SELECT MIN( gdb_from_date) FROM test_TB.dbo.ALA_DP_H1; -- From current Archive table
DECLARE @cutDate datetime
SELECT @cutDate = MIN( gdb_from_date) FROM test_TB.dbo.ALA_H1;
INSERT INTO dbo.ArcCutDate(cutDate,TableName) VALUES (@cutDate, 'ALA'); -- insert to current version feature class

[KevinDunlop]

LUPAPWC was the name of the feature class I used for the example (it was the one I had open at the time I made the post).  The archive always pulls from the default version of the feature class so my script does the same.  There is currently no archives for user versions.

The ArcCutDate is a table is used solely for this process.  I created so you can run multiple rebuilds at once (I recommend against this since this process can be a resource hog especially on very large feature classes).  That is why it stores the table name along with the min date.  Iit doesn't use any version and is not part of SDE.

Hope that helps.