Import .bacpac on premises

Sep 21, 2011 at 4:28 PM

Hi,

I successfully created backups for my SQL Azure Databases.

When I try to import a bacpac into my local SQL Server 2008 R2, it crashes with an error stating "Invalid Object Name : "Exploitation.xxx"

I checked, the object "Exploitation.xxx" is a synonym that exists on my database on SQL Azure

Any clue ?

Sep 21, 2011 at 6:33 PM

I'm trying to import a bacpac into SQL Azure using web interface, I don't have the same error, but I am stuck into "running" state since several hours.

Sep 21, 2011 at 6:56 PM

Ok I had more details by using DacSvcCli -STATUS :

'ONLINE INDEX DDL WITH LARGE OBJECT' is not supported in this version of SQL Server

I don't know what it means

Sep 21, 2011 at 9:11 PM

rodbeck, Thanks for reporting, we are looking into this issue.

For our diagnostic purposes, would you be able to provide a sample bacpac (without sensitive data/schema you don't want to share) that can repro this issue?

If so, can you please contact me at my adityasa @ microsoft.com ?

Sep 22, 2011 at 1:31 PM

Hi,

Some updated on the issue :

The import on Azure seems to work now ! The error was due to one of my stored procedure that contained a statement with a db name (like select * from DBNAME.SchemaName.TableName)

Note 1: I think the restore should ignore this kind of errors and not stop : I have several bacpac files that cannot be restored at this time because of a simple syntax error.

Note 2: Do you know if it would be possible to edit those previous bacpac to correct this error manually ?

The import on my computer still crashes with the same error, I send you a .bacpac file by mailfor you to investigate

Thanks a lot for your help !

Sep 22, 2011 at 6:42 PM

Hi rodbeck, To clarify are you talking about a different failure or the cause of 'ONLINE INDEX DDL WITH LARGE OBJECT' was this stored proc containing three part name?

If it was a different error, can you please paste that error as well?

In general it will be possible (see the caveats below) to edit the previous bacpacs (or dacpacs) manually as follows:

1) Rename the .bacpac (or .dacpac) file to .zip. Extract the contents to <path>. Navigate to <path>.

2) You should be able to see LogicalObjectStream.xml (or ObjectStream.xml). This file should contain all stored procedures (alongwith other objects in your database).

Edit and save this file.

5) Zip this folder and rename it back to .bacpac (or .dacpac as appropriate).

Depending on what kind of change is made, the loading/deployment/upgrade/any other dac operations may or may not fail.

 

Sep 22, 2011 at 7:02 PM

Hi,

I no longer have the INDEX error.

The only remaining error is when I try to import a bacpac on my local SQL Server 2008 R2.

I use DacImportExportCli.exe on the file I sent you by email.

Here is the error I get :

C:\Users\rbeck.TS-OFFICE\Downloads\DacImportExportCli-V-1-1>DacImportExportCli.exe -S .\SQL2008R2 -E -D RESTOREDTSVIDE -F C:\work\DbBackup\Backup_TSVIDE_201109221205183.bacpac -I
Microsoft (R) DAC Import Export Sample version 1.1.0.0
Copyright (C) Microsoft Corporation. All rights reserved.

Import started: 22/09/2011 19:59:15
Connecting to .\SQL2008R2...
Connection Open.
[19:59:55] PrepareSystemTables: Pending Preparing DAC metadata in the SQL Server instance 'RBECK-L\SQL2008R2'
[19:59:58] PrepareSystemTables: Success Preparing DAC metadata in the SQL Server instance 'RBECK-L\SQL2008R2'
[20:00:03] CreateDatabase: Pending Creating database 'RESTOREDTSVIDE'
[20:00:09] CreateDatabase: Success Creating database 'RESTOREDTSVIDE'
[20:00:09] ScriptDACObjects: Pending Preparing deployment script
[20:01:25] ScriptDACObjects: Success Preparing deployment script
[20:01:25] CreateDatabaseObjects: Pending Creating schema objects in database 'RESTOREDTSVIDE'
[20:01:34] CreateDatabaseObjects: Failure Creating schema objects in database 'RESTOREDTSVIDE' Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a
Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Invalid object name 'Exploitation.FormulairesEvaluateurEtat'.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute()
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute(IExecutionContext executionContext, TransactionalActionManager transactionalActionManager, IEnumerable`1 actionGroups, ScriptT
arget scriptTarget, Boolean rollbackOnFailure, Boolean sendEvents)
   at Microsoft.SqlServer.Management.Dac.CreateDatabaseObjectsStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.TransactionalStep.DoExecute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.Execute()
[20:01:34] CreateDatabase: RollbackPending Creating database 'RESTOREDTSVIDE'
[20:01:35] CreateDatabase: RollbackSuccess Creating database 'RESTOREDTSVIDE'
DAC Exception: Microsoft.SqlServer.Management.Dac.DacException: Unable to import bacpac. See inner exception for more details. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An
exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Invalid object name 'Exploitation.FormulairesEvaluateurEtat'.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute()
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute(IExecutionContext executionContext, TransactionalActionManager transactionalActionManager, IEnumerable`1 actionGroups, ScriptT
arget scriptTarget, Boolean rollbackOnFailure, Boolean sendEvents)
   at Microsoft.SqlServer.Management.Dac.CreateDatabaseObjectsStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.TransactionalStep.DoExecute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ExecuteActionSteps(IEnumerable`1 managedActionSteps)
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.Go()
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(Stream stream, DatabaseDeploymentProperties deploymentProperties, Boolean skipPolicyValidation)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(Stream stream, DatabaseDeploymentProperties deploymentProperties, Boolean skipPolicyValidation)
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(DatabaseDeploymentProperties deploymentProperties, String path, Boolean skipPolicyValidation)
   at DacImportExportCli.Program.ImportAction()
Import Complete.  Total time: 00:02:17.8438092

Thanks !

Sep 23, 2011 at 1:36 AM

This is a known issue that will be fixed in the next version of client-side tools. Please wait for the release of this version.

Sep 23, 2011 at 8:55 AM

Perfect ! I'll wait then.

Thanks

Jul 18, 2012 at 12:59 PM

hey can anyone give me a detailed description of how to obtain a .bacpac file form on premise sql server 2008 r2 edition database. specify the tools i need to install & the method to use them

Jul 21, 2012 at 12:44 AM

@kart10

You can download SQL Server Management Studio (SSMS) for SQL Server 2012.  The download link is here.

http://www.microsoft.com/en-us/download/details.aspx?id=29062

ENU\x64\SQLManagementStudio_x64_ENU.exe

After installing, use SSMS to connect to your SQL Server 2008R2 database.  Right click -> tasks -> export data-tier application

You can also create a bacpac programmatically using the Dac API.