3

I have a set of Coded UI tests which run on a daily basis via VSTS. One pre-requisite of this test plan is that a BACPAC be imported to the SQL server. In order to meet this, I created a test method which imports the BACPAC using the Microsoft.SqlServer.Dac.DacServices.ImportBacpac method, included the test in a test suite and then executed that test suite via a Run Functional Test task in the build definition.

Recently the database was moved to Azure and therefore this test is failing with the following message:

Microsoft.SqlServer.Dac.DacServicesException: Data cannot be imported into target because it contains one or more user objects. Import should be performed against a new, empty database. Error SQL0: Data cannot be imported into target because it contains one or more user objects. Import should be performed against a new, empty database.

A Request To The Veterans

Before I get to my question I would like to mention that my experience with Azure is currently nil and I hope you will excuse me if I ask anything that should have been obvious.

Question

Is there a way for me to import the BACPAC to Azure programmatically (similar to my existing approach)? Or is it a better idea to use Azure SQL Database Deployment task? Or is there another approach that you would recommend?

Rusty Wizard
  • 531
  • 6
  • 20
  • so, does google work for you? you can use powershell\tsql\arm templates (at the very least) to do this. – 4c74356b41 Aug 17 '17 at 06:49
  • @4c74356b41 Google does work for me but that article never showed up despite various search criteria. Maybe I wasn't using the necessary keywords. Or maybe it's because none of the keywords I used are mentioned in the article. Also, that seems to be complicated method to do a simple task and for someone, such as myself, with nil experience in Azure, there was a lot that didn't make sense. Thanks for taking the time to respond though. – Rusty Wizard Aug 17 '17 at 09:13
  • What's the result now? Do you solve this issue? – starian chen-MSFT Aug 22 '17 at 05:15

2 Answers2

1

The better and simple way is using Azure SQL Database Deployment task to deploy bacpac to azure database, it can add and delete firewall rule automatically.

On the other hand, you can refer to this thread below to add firewall rule to azure SQL server for your test machine or you can add it manually:

Deploy Dacpac packages via power shell script to Azure SQL Server

Update (build tasks):

  1. Visual Studio Build task to build database project (MSBuild Arguments: /p:OutDir=$(Build.ArtifactStagingDirectory))
  2. Azure SQL Database Deployment task (DACPAC File: $(build.artifactstagingdirectory)\**\*.dacpac)

If you are deploying database in release:

Build tasks:

  1. Visual Studio Build task to build database project (MSBuild Arguments: /p:OutDir=$(Build.ArtifactStagingDirectory))
  2. Publish Build Artifacts (Path to Publish: $(build.artifactstagingdirectory))
  3. Link release artifact to that build

Release tasks:

  1. Azure SQL Database Deployment task (DACPAC File: $(System.DefaultWorkingDirectory)\**\*.dacpac)
starian chen-MSFT
  • 33,174
  • 2
  • 29
  • 53
  • The reason I was looking for a programmatic approach is because I currently don't have access to the customer's Azure portal. I will speak to them and see if I could get access to it. Thanks! – Rusty Wizard Aug 17 '17 at 09:19
  • @RustyWizard The thread in my answer uses PowerShell to deploy database, you can refer to that code to do deploy. (note: it needs publish profile with user id and password that can be access to azure SQL Server) – starian chen-MSFT Aug 17 '17 at 09:59
  • @RustyWizard With that way, you don't need the permission to access to azure portal. – starian chen-MSFT Aug 17 '17 at 10:04
  • Oh right. I've submitted a request for permission. If that fails, I'll probably have to rely on the the PowerShell script approach. – Rusty Wizard Aug 17 '17 at 12:47
  • @RustyWizard Feel free to post the result here. – starian chen-MSFT Aug 18 '17 at 01:33
  • @starainMSFT The client attempted to be give me permission but despite assigning them to me, it didn't see to reflect on Azure portal. Anyways we used another account to set it up however I get this _##[error]No files were found to deploy with search pattern C:\Build2\1\s_ when the task is executed. I haven't specified a **Publish Profile** or **Additional SqlPackage.exe Arguments** though and **Azure Classic** connection type. Any thoughts on why this might be happening? – Rusty Wizard Aug 22 '17 at 08:22
  • @RustyWizard I added build and release tasks in my answer, refer to it to modify your build/release definition. – starian chen-MSFT Aug 23 '17 at 01:40
  • With regards to Step 2 in the Update section, I've given the TFS path to the DACPAC itself (as in $/Development/Team/DEV/AutomationSolution/AutomationProject/Data/BACPAC/DbBackup.bacpac). Can't I use this instead? – Rusty Wizard Aug 23 '17 at 06:41
  • @RustyWizard You can. Can you provide the detail log here? – starian chen-MSFT Aug 23 '17 at 06:46
  • @RustyWizard What's the detail setting of Azure SQL Database Deployment task? Set system.debug to true, then queue build and share the log on the OneDrive. – starian chen-MSFT Aug 23 '17 at 09:05
  • I've included screenshots of the Azure SQL Database Deployment task settings in the file https://1drv.ms/u/s!AqEAlsa7waJLggDAMbk_2d1Tz3YB – Rusty Wizard Aug 23 '17 at 12:36
  • @RustyWizard Try to delete Azure SQL Database Deployment task> Save definition> Add Azure SQL Database Deployment task again. You also can try to create a new definition with these tasks. – starian chen-MSFT Aug 24 '17 at 02:40
  • What's the result of you using $(build.sourcesdirectory)\Customer.Bookkeeper.Automation.Coded\Data\BACPAC\\*.bacpac? – starian chen-MSFT Aug 24 '17 at 08:11
  • Try it with Hosted build agent and check the result. – starian chen-MSFT Aug 25 '17 at 01:40
  • This time the Publish Build Artifacts task (which was prior to the Azure SQL Database Deployment task) failed. So I tried disabling that but it failed with the error: "Invalid Dacpac file 'd:\a\4\s\Customer.Bookkeeper.Automation.Coded\Data\BACPAC\DbBackup.bacpac' provided". Logs - https://1drv.ms/u/s!AqEAlsa7waJLggEKIGLSFCWkaiBa – Rusty Wizard Aug 25 '17 at 12:14
  • @RustyWizard How do you get/generate bacpac file? Can you reproduce this issue with a new database project? – starian chen-MSFT Aug 28 '17 at 02:28
  • I'm using the same bacpac I used before the move to Azure. I generated the bacpac via SSMS by right clicking on the database and selecting **Tasks** > **Export Data-tier Application...**. I'm able to restore the bacpac to Azure via SSMS though. Regarding creating a new database project, do you mean by attempting to restore a different bacpac? – Rusty Wizard Aug 28 '17 at 08:08
  • @RustyWizard You need to do it with a [database project](https://msdn.microsoft.com/en-us/library/hh272677(v=vs.103).aspx) – starian chen-MSFT Aug 28 '17 at 08:47
  • Thanks for the link. When I tried "To create a new database project off a connected database" and published (after deleting the database) the DB was restored successfully. I tried "To create a new project and import existing database schema" but when I clicked on **Start** (step 4) I got the message, "The server version or database compatibility level is not supported". I am using Visual Studio 2015 by the way. Does this shed any light on my issue by any chance? – Rusty Wizard Aug 30 '17 at 05:56
  • What' the version of your SQL Server? What's the Target Platform of database project? (Right click the project=> Properties=>Project Settings) – starian chen-MSFT Aug 30 '17 at 06:25
  • I have SQL Server 2016 installed on my machine and the Target Platform is Microsoft Azure SQL Database V12 – Rusty Wizard Aug 31 '17 at 02:10
  • What's the result if you change Target Platform to SQL Server 2016? – starian chen-MSFT Aug 31 '17 at 08:01
  • I'm able to import the database but when I try to publish it I get the message, "A project which specifies the SQL Server 2016 as the target platform cannot be published to Microsoft Azure SQL Database v12". – Rusty Wizard Sep 01 '17 at 09:03
0
--Backup And Restoring BACPAC File In Azure Portal
--Step :-
1)Go to Database  >>Task >>Export Data
This Will Create A BACPAC File.

2)connect to Azure Portal Right CLick On Database >> Import Data 
This will Import the Data Database In Azure Sql Portal
NOTE :- Azure Not Allow to Import data If The DataBase BACPAC File Contain
        Any Encryption Data/File.

--=====For Blob Data in Azure Portal >>Download Microsoft Azure Storage Tools>>======
--Step  
--1)Create Storage Account And Credential Of Azure Account
--2)Go to Data IN Storage Account And Import the Bacpac file
--This will upload the BACPAC File ON Azure Portal
Alfaiz Ahmed
  • 1,698
  • 1
  • 11
  • 17