6

I'm using SQL Server 2012 , I know how to take bacpac of sql database from azure portal and store that file into blob but

How do I generate bacpac file from local machine and upload it on Azure blob?

Is there any way to do so ? using c# program or any utility ?

Neo
  • 15,491
  • 59
  • 215
  • 405
  • Have you checked DacFx framework? – Dmitrij Kultasev Mar 31 '15 at 08:56
  • actually i'm trying with this posts `http://www.sqlshack.com/backup-sql-server-databases-microsoft-azure/` but I do not get any options in Backup like URL prefix :( no I have not tried DacFx – Neo Mar 31 '15 at 09:01
  • *SQL Management Studio 2014* can export a database to a .bacpac file. Try to use the latest management studio, connect to your *SQL 2008 server* and see if you can export the bacpac. – Cyril Durand Mar 31 '15 at 09:14
  • earlier versions of studio can do that too. The only thing is that (at least with dacpacs), you can not do that from the studio if you have any errors in database (missing objects are used in stored procedures, linked servers etc) – Dmitrij Kultasev Mar 31 '15 at 09:16
  • I think http://www.red-gate.com/products/sql-development/sql-comparison-sdk/ will suit your need. – sudhansu63 Mar 31 '15 at 09:38

2 Answers2

10

If you are looking to automate this, there is a way:

1) Generate the .bacpac file using SqlPackage.exe

For example:

“C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe” 
/a:Export /ssn:SourceServerName /sdn:SourceDatabaseName      
/tf:C:\DataExtraction\SourceDatabase.bacpac"

This would generate a bacpac under C:\DataExtraction\SourceDatabase.bacpac

for more info go here: SqlPackage.exe

2) Upload the bacpac to Azure Storage as Blob using Azure PowerShell

Switch-AzureMode -Name AzureServiceManagement
$context= New-AzureStorageContext -StorageAccountName "Storageaccountname" -StorageAccountKey "mystoragekeyhere"

Set-AzureStorageBlobContent -Context $context -Container adventureworks -File "NameOfLocal.bacpac" -Blob "NameofBacpacInStorageContainer.bacpac"

for more information on this cmdlet go here: Set-AzureStorageBlobContent

Should you need to Import the following command will help you:

“C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe” 
/a:Import /tsn:TargetServerName /tdn:TargetDatabaseName      
/sf:C:\DataExtraction\SourceDatabase.bacpac"

Should you desire to use sqlpackage.exe add the following directory to your PATH (instructions):

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\

pim
  • 12,019
  • 6
  • 66
  • 69
Boboyum
  • 708
  • 3
  • 11
  • Would i also be able to import this on an older SQL server on a different on-premise server? been trying and failing a 100 different ways to achieve this. :( – Brunis May 07 '21 at 07:49
6

Download and install SSDT tool

[Your DataBase]-> Task -> Export Data tier Application -> [Choose the localPath.bacpac]

You can also directly deploy to SQL Azure using Deploy Data Tier Application to SQL Azure

sudhansu63
  • 6,025
  • 4
  • 39
  • 52