Home Dashboard Directory Help
Search

SQL Azure fires a trigger when restoring from bacpac by Jan Vilímek


Status: 

Active


6
0
Sign in
to vote
Type: Bug
ID: 781674
Opened: 3/19/2013 7:44:44 AM
Access Restriction: Public
3
Workaround(s)
view
3
User(s) can reproduce this bug

Description


We have several triggers on our tables. One of many is like

CREATE TRIGGER yyyy ON xxx FOR INSERT, UPDATE, DELETE AS RAISERROR(''You are not allowed to modify the table xxx',13,1); ROLLBACK TRAN;

This table contains data. When the database is backed up to BACPAC via Azure management portal, it goes fine. When we try to restore such BACPAC, it throws an error because the trigger is fired when inserting data to the table.

This is wrong. We have several triggers that guards DB integrity (business logic beyond FK keys, constraints) and we need to have such triggers in DB.
The correct way is to DISABLE all triggers before inserting (bulkinsert?) data from BACPAC.


PS: few moths back everything was fine when restoring BACPAC while having triggers in DB model
Details
Sign in to post a comment.
Posted by Jan Vilímek on 4/11/2014 at 1:49 AM
Martin, thanks for information. Do you know the ETA for this? I have reproduced the error on SQL Server Management Studio 2014 with the Azure database 11.0.9208
BR Jan
Posted by Microsoft on 2/11/2014 at 9:00 PM
Jan,

Thank you for the feedback. We have fixed this now by disabling and reenabling triggers during the data deployment phase. It will be available in the next major release of DacFx.

Thanks,

Martin
Sign in to post a workaround.
Posted by JasonCrease on 4/8/2014 at 9:24 AM
A good description of how to edit a DACPAC is here: http://sqlproj.com/index.php/2013/02/how-to-add-objects-to-master-dacpac/ . DACPACs and BACPACs are sufficiently similar such that this approach works.

From what I can tell, the checksum for the model.xml is SHA-256, not SHA-512.
Posted by Rodolphe Beck on 11/14/2013 at 6:03 AM
Hi,

I have many triggers on my database, so it is painfull to deactivate/delete them then reactivate them.

I use the following process to import a database :
- Launch the bacpac import, it creates the schema then crashes.
- Unzip the .bacpac on my hard drive
- Launch a PowerShell script to import the data using bcp

Here is an example of PS script :

$database_name="<DATABASE_NAME>"

$directoryEntries = [IO.Directory]::GetDirectories("C:\temp\$database_name\Data");
foreach($directoryName in $directoryEntries)
{
    $dir = Split-Path $directoryName -Leaf
    $fileEntries = [IO.Directory]::GetFiles($directoryName)
    foreach($fileName in $fileEntries)
    {
        Write-Output "bcp $dir in $fileName -N -T -d $database_name"
    }
}
Posted by Jan Vilímek on 3/19/2013 at 7:46 AM
before restoring data from BACPAC modify model.xml and remove problematic triggers. You have to calculate SHA-512 and modify origin.xml also. After succesfull restore you have to re-create affected triggers.