Restoring SQL Server backup to Azure SQL Database

Question

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.

Answer 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.

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

How to migrate SQL Database to Azure SQL Database using SSMS deploy

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

Answer 2

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

Answer 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.

2. 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.)

Answer 4

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:

Follow the wizard that appears then.

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