none
Identity seed is incorrect after restore of Azure SQL Database from bacpac RRS feed

  • Question

  • We have bacpac files for database backup retention for more than 35 days as part of our DRP, and for restoring into test environments.

    What we have found is that after restoring a database from an azure export bacpac file the identity seed in a table has not retained the value in the original database.

    For example, latest ID is 100. That record is deleted, then

    • the IDENT_CURRENT value for that field is 100
    • latest ID in that table is now 99.
    • if a new row was added the ID would be 101

    If that database is then exported to bacpac and imported as a new database,

    • latest ID in the table is 99
    • the IDENT_CURRENT value for that field now 99
    • if a new row was added the ID would be 100

    Is there a setting or command to retain the value of Identity Seeds on all tables in the database when it is restored from a bacpac?

    Thursday, December 5, 2019 12:20 AM

Answers

  • Thanks Navtej,

    I don't think the PG has quite replicated the scenario when considering their reply. The scenario I described clearly shows the issue that the seed values would not be the same on both systems. 

    And, the work-around solution will be to build a script to detect all tables with an identity column (116 for us), and then for each table, determine if it has a history/temporal table attached, then get the max ID field from both tables, compare to the seed, and if different, reseed the table. Once we build this script then yes running this after a restore will not take a long time. But I wouldn't class it as really simple. 

    And another problem that I have thought of, which is actually more serious if you are not to reuse ID fields, is if you don't have a history table, then you wouldn't know if the last record was deleted (e.g. ID 103 was the last record in the table, and it was deleted just before the bacpac was created), so if you then added a new record after restoring from bacpac the next id would be 103. This might mean for those tables an identity field should never be used as you could not be guarenteed that an ID would never be reused in the case of a restore from bacpac. All to be considered as part of a DRP...

    Anyway, thank you for pursuing, I appreciate you following this up for me

    Regards,
    Gerri

    Monday, December 16, 2019 10:45 PM

All replies

  • Hi

    I just did the testing and cannot reproduce the problem.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 5, 2019 4:47 AM
  • Hi Uri, that's interesting, as I've been able to reproduce consistently.

    Myabe my steps weren't clear enough

    To replicate:

    In original database A: 

    1. latest ID in table Customer is 100
    2. Delete record with ID 100
    3. latest ID in Customer table is now 99.
    4. the IDENT_CURRENT value for that field is 100 (SELECT IDENT_CURRENT('Customer'))
    5. Export database A to bacpac

    Import bacpac as database B (onto a different azure sql server)

    1. latest ID in Customer table is 99
    2. the IDENT_CURRENT value for that field is 99 - but should be 100 as in database A
    3. add a new row - the ID is 100 

    Also, not sure if it would have any impact, but we have a temporal table attached to the Customer table for logging changes to records


    Thursday, December 5, 2019 7:57 PM
  • Hi Gerri

    We were able to repro this.We are going ahead and escalating this to our PG team.

    Thanks
    Navtej S

    Friday, December 6, 2019 12:25 AM
    Moderator
  • Thank you Navtej - pardon my ignorance, but who is the PG team?

    Regards,
    Gerri

    Friday, December 6, 2019 3:52 AM
  • Hi Gerri

    The error is on my part. The PG team is Product Group. They manage the builds and issues of a particular product.

    Regards
    Navtej S

    Friday, December 6, 2019 5:34 AM
    Moderator
  • Yep, thanks, I also was able to reproduce it, looks like a bug

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, December 8, 2019 5:21 AM
  • Hi Gerri

    The confirmation from product group is that the values is retained when you backup /restore the database but not when you bacpac.

    Please provide your particular scenario and how critical is the dependency on this value from IDENT_CURRENT.

    Hope to work further on this to provide answer for you on your particular scenario.

    Thanks
    Navtej S

    Thursday, December 12, 2019 9:48 PM
    Moderator
  • Hi Navtej,

    We use this in two scenarios.

    1) for DRP - we take offsite backups of the azure databases as bacpacs, and retain in case the 35 day online backups are not available. 

    It would be restored back into a production environment, at which point, wth the current situation, we would need to reset the identity seed on all tables with identity columns, checking both the main table and the history/temporal table (113 tables)

    2) we restore copies of the production database into our dev and uat environments. In this scenario it is not as vital for the identity seed to be correct, but makes validation of data much more challenging as we compare both current and history tables to see effects of updates/deletes etc.

    So, for both scenarios, without a fix from Azure, we will need to write a script to identify the correct seed and reset for all tables after a restore from bacpac (adding to what is already quite a long process).

    Regards,
    Gerri


    • Edited by Gerri M Thursday, December 12, 2019 10:43 PM changed number of tables
    Thursday, December 12, 2019 10:38 PM
  • Hi Gerri

    According to the PG team

    "This is not a product error. Identity is a column based property and is part of the table metadata.

    BACPACs are logical data copy and is not considered a transactional consistent copy of the data, which means we will carry over the table metadata but only the create table statement as at time of backup. This has been the case since we introduced app-tier backups.

    Identity seeds run sequential as rows are inserted in to a table, so you would run seed 1 – n, where n is number of rows. For example if you inserted a 100 rows you would expect last row to be identity 100. However if during the 100 row load you deleted rows from the same table the COUNT(1) and Seed number for the last row would differ. i.e we do not back fill on seeds. Here is an article with a sample script to look for seed gaps: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15

    So you will find that if you had to replay every single transaction between the time the BACPAC was taken and restored you would have the same seed value on both systems. The problem that your running into is that rows are either being inserted then deleted or older rows deleted, which would lead to this inconsistency, and it’s easy to replicate this behavior

    The resolution is really simple and will take them a few extra seconds. You can use the DBCC CHECKIDENT ( https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15) to compare the identity seed on the source table and set the value respectively on the target location."

    Also they have created a backlog item on their end for this which means they may work on this to add functionality in BacPac where it doesn't lose the value.

    Hope this helps.

    Thanks
    Navtej S

    Monday, December 16, 2019 2:32 PM
    Moderator
  • Thanks Navtej,

    I don't think the PG has quite replicated the scenario when considering their reply. The scenario I described clearly shows the issue that the seed values would not be the same on both systems. 

    And, the work-around solution will be to build a script to detect all tables with an identity column (116 for us), and then for each table, determine if it has a history/temporal table attached, then get the max ID field from both tables, compare to the seed, and if different, reseed the table. Once we build this script then yes running this after a restore will not take a long time. But I wouldn't class it as really simple. 

    And another problem that I have thought of, which is actually more serious if you are not to reuse ID fields, is if you don't have a history table, then you wouldn't know if the last record was deleted (e.g. ID 103 was the last record in the table, and it was deleted just before the bacpac was created), so if you then added a new record after restoring from bacpac the next id would be 103. This might mean for those tables an identity field should never be used as you could not be guarenteed that an ID would never be reused in the case of a restore from bacpac. All to be considered as part of a DRP...

    Anyway, thank you for pursuing, I appreciate you following this up for me

    Regards,
    Gerri

    Monday, December 16, 2019 10:45 PM
  • Hi Gerri

    Just to confirm again the PG team has created the back log item which means they will be considering to fix this but it has no ETA attached to it.

    Additionally please do think about using PITR or Geo Restore which will maintain the transactional sanctity of your DB.

    Thanks
    Navtej S

    Wednesday, December 18, 2019 3:02 PM
    Moderator