Migrating On-Premises SQL Database to Azure

Backup & Restore On-premise DB To-From AZURE STORAGE ACCOUNT

On-premise DB To-From AZURE STORAGE ACCOUNT:

First need to create Storage Account, Search storage like below…

Provide Name, Location, Replication, Select Resource Group and select Create… 

Submitted deployment of storage-account

testazuredbstorage account created successfully…

Check property of created storage account

Now got to Access Key in storage account and check you will get 2 key which can be used while performing backup & restore operation. 

Now come to Container we required this to store backup files. Click + to add new container

Provide name of Container and provide access level.

Container has been created… 

Goto Container Properties you will get Container URL which we will use while backup-restore 

Connect Local SQL Server, create CREDENTIAL as shown below INDENTITY will storage account name , SECRET will be one of access key which you got under Access Key of storage account. 

---

Backup database:

Now perform backup operation by using URL which you got from CONTAINER properties, provide bak file name as shown below & also provide CREDENTIAL name which you created earlier.  Backup has been completed successfully as shown below.

Check backup file will appear under CONTAINER 

If you want to restore backup saved at Azure storage then select bak file which you want restore and copy backup URL as shown below.  

---

Queries Used in above Demo

Access Key 

9Ov8CL2jGEHB1xkUcIIkXxGeItkGc0omJPLQLAnF8AaY7dj0NkgqyawwZgDzLKP0J7IGeclcp4h7g/DmMXl1aw==

Container URL

https://testazuredbstorage.blob.core.windows.net/azurebkpcontainer

Query to create CREDENTIAL

USE master
GO
CREATE CREDENTIAL AzureBackups --give this a meaningful name
WITH IDENTITY='testazuredbstorage', --storage account name:
SECRET = '9Ov8CL2jGEHB1xkUcIIkXxGeItkGc0omJPLQLAnF8AaY7dj0NkgqyawwZgDzLKP0J7IGeclcp4h7g/DmMXl1aw==' --storage account key from portal
GO

Query to Create Backup

BACKUP DATABASE WideWorldImporters 
TO URL = N'https://testazuredbstorage.blob.core.windows.net/azurebkpcontainer/WideWorldImporters.bak'
WITH credential = 'AzureBackups'   --credential name
GO

Query to Restore Backup

RESTORE DATABASE WideWorldImporters 
FROM URL = N'https://testazuredbstorage.blob.core.windows.net/azurebkpcontainer/WideWorldImporters.bak'
WITH credential = 'AzureBackups'   --credential name
GO

Query to change IDENTITY name

USE [master]
GO
ALTER CREDENTIAL AzureBackups WITH IDENTITY = 'testazuredbstorage', 
SECRET = N'9Ov8CL2jGEHB1xkUcIIkXxGeItkGc0omJPLQLAnF8AaY7dj0NkgqyawwZgDzLKP0J7IGeclcp4h7g/DmMXl1aw=='
GO