76

Is there a way to simply take a SQL backup .bak file, and using that as a restore point to generate a new database on Azure SQL Database?

The wizard that I see generally recommended in this situation wants to create a custom file to use, but it pukes on the encrypted objects ( functions and views ) and I don't see another way to get it going.

Neo
  • 3,309
  • 7
  • 35
  • 44

5 Answers5

118

If you already have a database on local machine then you can migrate it directly on Azure using SQL Management Studio. Follow these steps:

Right click on the database name in SSMS: mssms

Follow the wizard that appears then.

If you're running SSMS v17 you can configure the Azure SQL database edition, size and service objective.

Bern
  • 7,808
  • 5
  • 37
  • 47
razon
  • 3,882
  • 2
  • 33
  • 46
  • 6
    Thanks, it works fine. I just had to add my IP on Firewall settings of database server. – Carlos Coelho Jan 10 '17 at 18:58
  • 7
    If attempting to restore .bak for the sake of getting a database and it's data into azure, it's definitely much easier to just deploy using this method – hanzolo May 23 '17 at 06:05
  • 1
    Note that the export will fail with very obscure error message if you connect to Azure SQL database using `Active Directory - Universal with MFA support` authentication. Use `SQL Server Authentication` instead – fjch1997 May 17 '18 at 21:15
  • 3
    For future readers: I strongly believe that restoring the backup to a local db and doing this is easier than migrating using BACPAC files manually (this will create the BACPAC files) – Gaspa79 Jun 13 '18 at 13:44
  • does the db needs to exists? or it creates by itself? – Kreker Jul 19 '19 at 15:31
  • I added outbound rule for port 1433. – L0uis Jul 21 '20 at 19:54
  • 1
    @Kreker, it will create the db and fail if it already exists (was my experience). – tristram Aug 23 '21 at 13:59
28

Azure SQL Database does not currently support that functionality. The only method to restore in Azure SQL Database is import from BACPAC files - you can migrate to Azure SQL DB using BACPAC files by following this guide: https://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate/

The other options is to use SQL Server in a VM, which would enable a restore of a .bak file

Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37
  • 2
    Is this true now as well??? I have to achieve restoring .bak files to Azure SQL using Powershell.. Can this be done or it is dead end? – zainul Jan 18 '17 at 05:44
  • @fjch1997 zainzul does not say he has achieved restoring from .bak files - rather he says that he HAS TO achieve that. He is asking if it is possible. – Morten Jensen May 18 '18 at 12:53
  • It is better to use the DMA and it will generate the possible changes in the DB. It will also generate the query to migrate the information. It is not necessary to upload a bak file. Probably, it is possible. But, I recommend you use the DMA program. It is cleaner and it will show you errors between the local Sql instance and the version on the cloud. – Juan Acosta Feb 02 '19 at 05:50
3

To get my .BAK file on Azure SQL Server, I followed the advice of both answers in this thread.

Before completing the below steps the BAK file restore failed on my laptop SQL instance (the backups were from SQL2016 and not compatible with my SQL2014). I was working on training from EDX.org: “DAT216x Delivering a Relational Data Warehouse” and I had downloaded these two files from the training: AdventureWorks2016CTP3.bak and AdventureWorksDW2016CTP3.bak.

BAK to AzureDB

  1. USER: Joseph Idziorek | ANSWER: Use SQL Server in a VM, which would enable a restore of a .bak file

I followed these 2 videos (hosted on YouTube) to create the Azure VM and SQL Instance to be used in MCA training… Demo: Provisioning Microsoft Azure Services (VM, SQL). The 9:00-minute video shows you how to provision the relevant Azure resources. The video also demonstrates how to get the connection string for the Azure SQL Database, which is used in the next step.

From the Azure VM remote session connection to SSMS I did the Restore Database from BAK.

  1. USER: razon | ANSWER: Right click (database name) > Tasks > Deploy Database to SQL Azure…

From the Azure VM remote sessions connection to SSMS SQL Instance, I ran the wizard “Deploy Database to SQL Azure”. After connection successfully the deployment took a few minutes to complete. NOTE: you must use ‘SQL Server Authentication’ because ‘Windows’ is not supported for Azure database. The Azure SQL Server authentication was demonstrated in the video link provided earlier in this answer.

Deployment environments

@@servername @@version

+------------------------------------------------------------------------------------------------------+
| Source                                                                                               |
+------------------------------------------------------------------------------------------------------+
| data216x                                                                                             |
|                                                                                                      |
| Microsoft SQL Server 2016 (SP1-CU13) (KB4475775) - 13.0.4550.1 (X64)                                 |
| Jan 10 2019 19:31:11                                                                                 |
| Copyright (c) Microsoft Corporation                                                                  |
| Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) |
+------------------------------------------------------------------------------------------------------+
| Target                                                                                               |
+------------------------------------------------------------------------------------------------------+
| sqlserver-dat216x                                                                                    |
|                                                                                                      |
|                                                                                                      |
| Microsoft SQL Azure (RTM) - 12.0.2000.8                                                              |
| Dec 19 2018 08:43:17                                                                                 |
| Copyright (C) 2018 Microsoft Corporation                                                             |
+------------------------------------------------------------------------------------------------------+

Results

The Deploy Database to Azure failed in my scenario on the step Importing database with this message. After I removed the incompatible objects the Azure DB deployment was successful.

Importing database...

TITLE: Microsoft SQL Server Management Studio

Could not import package.

Warning SQL0: A project which specifies SQL Server 2016 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.

Error SQL72014: .Net SqlClient Data Provider: Msg 40536, Level 16, State 2, Line 1

'MEMORY_OPTIMIZED tables' is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.

Error SQL72045: Script execution error. The executed script:

CREATE TYPE [Sales].[SalesOrderDetailType_inmem] AS TABLE (
    [OrderQty]       SMALLINT NOT NULL,
    [ProductID]      INT      NOT NULL,
    [SpecialOfferID] INT      NOT NULL,
    INDEX [IX_SpecialOfferID] NONCLUSTERED HASH ([SpecialOfferID]) WITH (BUCKET_COUNT = 8),
    INDEX [IX_ProductID] NONCLUSTERED HASH ([ProductID]) WITH (BUCKET_COUNT = 8))
    WITH (MEMORY_OPTIMIZED = ON);

(Microsoft.SqlServer.Dac)

BUTTONS:

OK

Screenshot:

After I removed the incompatible object the Azure DB deployment was successful. (NOTE: I had to do this a couple times. I tried Redgate SQLSearch for MEMORY_OPTIMIZED but it kept saying "no search results" even though the table definition did include the keyword.)

enter image description here

SherlockSpreadsheets
  • 2,062
  • 3
  • 27
  • 47
3

Try the "Data Migration Assistant"; https://learn.microsoft.com/en-us/sql/dma/dma-migrateonpremsqltosqldb?view=sql-server-ver15

DMA Download : https://www.microsoft.com/en-us/download/confirmation.aspx?id=53595

Jody Jacobus Geers
  • 364
  • 2
  • 7
1

Currently Azure doesn't support restoring the database on Azure SQL Instance using the backup (.bak) file. But there are many other ways to migrate the SQL Database to Azure SQL. For example, using the SQL Server Management Studio deploy mention or by taking the backup in the .bacpac file and then importing it using SSMS Export/Import or using the Microsoft database migration assistance tool. Below link will be useful to you as they explain these ways as step by step process.

StackzOfZtuff
  • 2,534
  • 1
  • 28
  • 25
vivek
  • 19
  • 1