CODEWITHSUNDEEP
sqlazuressmsbackuprestore
ReviveDigitalTeam
ReviveDigitalTeam

Reputation: 21

When trying to restore a database backup from Azure SQL on a local SSMS, it doesn't do it and shows errors

When trying to restore a database backup from Azure SQL on a local SSMS (v19.x), it can't proceed and shows errors:

Could not import package.

Warning SQL72012: The object [Identity_2023-02-14_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 [Identity_2023-02-14_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: Framework Microsoft SqlClient Data Provider: Msg 35221, Level 16, State 1, Line 1 Could not process the operation.

Always On Availability Groups replica manager is disabled on this instance of SQL Server. Enable Always On Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

Error SQL72045: Script execution error. The executed script:

ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [XTP_704A0C41], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_XTP_704A0C41.mdf') TO FILEGROUP [XTP];

Upvotes: 1

Views: 1234

Answers (3)

SuperMistaG
SuperMistaG

Reputation: 29

The error is very specific and does not reference the contained database authentication.

The problem occurs when the SQL server that you have exported your database from is part of an availibility/failover group as referenced in the error message. An error then occurs due to filegroups.

Error SQL72045: Script execution error. The executed script:

ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [XTP_704A0C41], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_XTP_704A0C41.mdf') TO FILEGROUP [XTP];

Solution: Extract the bacpac, edit the model.xml, look for the following (mine was near the bottom)

    <Element Type="SqlFilegroup" Name="[XTP]">
        <Property Name="ContainsMemoryOptimizedData" Value="True" />
    </Element>

Delete this element.

Update the Origin.xml hash by finding the new hash of model.xml

Get-FileHash .\model.xml -Algorithm 'SHA256' | Select-Object -ExpandProperty Hash

Other articles explain this in more detail, e.g. https://stackoverflow.com/a/50531532/6378698

Re-zip the directory into a new .bacpac file.

Upvotes: 0

Reilly McLaren
Reilly McLaren

Reputation: 1

I had a similar error and had a terrible time tracking down the issue. Tried the suggestions above with no luck. Turns out I just didn't have one of the add-on features installed for my sql server(specifically the full text search).

In order to fix this I simply installed the feature to my existing server(https://learn.microsoft.com/en-us/sql/database-engine/install-windows/add-features-to-an-instance-of-sql-server-setup?view=sql-server-ver16) and then the import worked perfectly. Maybe someone else will have a similar issue, hopefully this helps.

Upvotes: 0

vijaya
vijaya

Reputation: 1741

Error SQL72014

To resolve this error, you need to enable contained database authentication on the SQL Server instance as mentioned by @ShaktiSingh-MSFT

BY following @ Windhoek documentation you will find same issue is raised and resolved .

sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO

Here The sp_configure 'contained database authentication', 1 command is used to enable contained database authentication in SQL Server.

Refer this MS document for more information enter image description here You can Refer this SO thread also .

Upvotes: -1

Related Questions