none
Bacpac import error when Sync provisioning enabled

    Question

  • Hi guys

    I have a Azure db running with sync Services enabled.

    During the sync provisioning many functions are created.  like  get_new_rowversion()

    When i try to backup the database file now with an Export to .bacpac - this bacpac file cannot be imported to another database - which causes serious Problems.

    It's not possilbe for me to restore a backup?

    I tried with the latests Versions from todays SSMS and DAC.

    I'm always getting SQLExpection that get_new_rowversion() and all other sync related functions are not available.

    Monday, March 25, 2013 5:23 PM

All replies

  • Hi Tom,

    Could you please post the error you received when the export failed?

    Please try to follow this article to ensure the steps of exporting files are correct:

    http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx

    Try to use SQL Database Import/Export Service and notice the BEFORE YOU BEGIN part.

    If you have any feedback on our support, please click here.


    Iric Wen
    TechNet Community Support


    Thursday, March 28, 2013 8:36 AM
    Moderator
  • Hi Tom,

    Are you trying to restore the BACPAC to a local sql server instance? The get_new_rowversion() is a SQL Azure only function. When running data sync on SQL Azure database, it leverages this function.

    If you are just importing to local database for backup/restore purpose, you can exclude all the objects under the DataSync schema during export.

    Monday, April 8, 2013 2:54 AM
  • Hello.

    Yes I'm trying to Import a sql azure in a local instance of 2008R2.

    How do i do this?

    "If you are just importing to local database for backup/restore purpose, you can exclude all the objects under the DataSync schema during export."

    thanks

    Monday, April 8, 2013 6:25 AM
  • So my steps are:

    1) Export a bacpac file from SQL azure to my desktop

    The azure SQL is provisioned using Microsoft sync Framework 2.1

    2) Import the bacpac to a local instance of SQL 2008 R2

    This doesn't work and turns out in an error msg saying:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Could not import package.
    Warning SQL72012: The object [PRIMARY] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Warning SQL72012: The object [testasdf_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Warning SQL72012: The object [testasdf_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure zvgart_updatemetadata, Line 14 'get_new_rowversion' is not a recognized built-in function name.
    Error SQL72045: Script execution error.  The executed script:
    CREATE PROCEDURE [zvgart_updatemetadata]
     @P_1 NVarChar(4),
     @sync_scope_local_id Int,
     @sync_row_is_tombstone Int,
     @sync_create_peer_key Int,
     @sync_create_peer_timestamp BigInt,
     @sync_update_peer_key Int,
     @sync_update_peer_timestamp BigInt,
     @sync_check_concurrency Int,
     @sync_row_timestamp BigInt,
     @sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; DECLARE @nextRowVersion bigint; SELECT @nextRowVersion = get_new_rowversion(); DECLARE @was_tombstone int; SELECT @was_tombstone = [sync_row_is_tombstone] FROM [zvgart_tracking] WHERE ([ZvgartId] = @P_1);IF (@was_tombstone IS NOT NULL AND @was_tombstone = 1 AND @sync_row_is_tombstone = 0) BEGIN UPDATE [zvgart_tracking] SET [create_scope_local_id] = @sync_scope_local_id, [scope_create_peer_key] = @sync_create_peer_key, [scope_create_peer_timestamp] = @sync_create_peer_timestamp, [local_create_peer_key] = 0, [local_create_peer_timestamp] = @nextRowVersion, [update_scope_local_id] = @sync_scope_local_id, [scope_update_peer_key] = @sync_update_peer_key,
     (Microsoft.SqlServer.Dac)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Monday, April 8, 2013 4:20 PM
  • Same problem here. Any solution yet?
    Tuesday, May 28, 2013 11:09 PM
  • I had the same issues.  I'm also using Entity Framework with automatic migrations.  For both I've resorted to deprovisoning the dB before doing the operation.  So for a backup, I make a copy (new name) then deprovision it then do a bacpac export.  To restore I do a bacpac import then provision and a couple of renames gets my original dB back.  Never could get around backing up the sync tables.  Hope this helps.

    Donald Hofferber

    Monday, June 3, 2013 6:20 PM
  • I finally found a way to do this at http://blogs.msdn.com/b/sqlazure/archive/2010/05/17/10014014.aspx.  It will copy all of the tables directly from Azure, but you will need to do the stored procs by hand.

    Tom

    • Proposed as answer by Tom Layson Friday, August 9, 2013 11:54 PM
    Friday, August 9, 2013 11:54 PM