Backup On-Premise SQL Server Database to Microsoft Azure

Blog36_1

-> Creating a Storage Account,

Blog36_2.PNG

Blog36_3.PNG

-> Once the Storage account is deployed. Click on Blog36_4 Resources and then click on your Storage that we deployed just now. It is jbsqlbackups in my case,

Blog36_5.PNG

-> Click on Containers and add a new container.

Blog36_6.PNG

-> Click on Access Keys and track down your Storage Account Name and the Keys,

Blog36_7.PNG

-> Open SQL Server Management Studio and try a backup,

Blog36_8.PNG

Blog36_9.PNG

Blog36_10.PNG

Blog36_11.PNG

Blog36_12.PNG

-> Lets connect to the Azure storage and check the backup file,

Blog36_13.PNG

-> Copy the Access key that was noted earlier and paste it connect to the Azure Storage,

 

Blog36_14

Blog36_15.PNG

-> Lets try restoring the database,

Blog36_16

Blog36_17.PNG

-> Click on OK and you will get the below screen. Select the backup that you ant to restore,

Blog36_18.PNG

Blog36_19.PNG

Blog36_20.PNG

-> The database is restored and ready for use.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Restore on-Premise SQL server database to Azure SQL Database

-> We need to restore the database from on-Premise to Azure SQL database.

-> On-Premise database JB_test has two tables and let’s check the row count,

Blog2_1

1) Moving only the schema from on-Premise SQL database to Azure SQL database,

-> Right click on-Premise database -> Tasks -> “Extract Data-tier Application…” as shown in the screenshot below,

Blog2_2

-> Click on next,

Blog2_3

-> Provide the location where the file should be placed,

Blog2_4

Blog2_5

Blog2_6

-> Once the dacpac file is created. Get onto the SQL azure database, right click “Databases” and select “Deploy Data-tier Application”.

Blog2_7

Blog2_8

-> Specify the dacpac file,

Blog2_9

Blog2_10

Blog2_11

Blog2_12

-> It is complete and we see the database in Azure,

Blog2_13

2) Moving schema and data from on-Premise SQL database to Azure SQL database,

-> Right click on-Premise database -> Tasks -> “Export Data-tier Application…” as shown in the screenshot below,

Blog2_14

-> Click on next,

Blog2_15

-> Provide the location where the file should be placed,

Blog2_16

Blog2_17

Blog2_18

Blog2_19

-> Once the bcpac file is created. Get onto the SQL azure database, right click “Databases” and select “Import Data-tier Application”.

Blog2_20

Blog2_21

-> Specify the bacpac file,

Blog2_22

Blog2_23

-> When I clicked “Next” I got the below message,

Blog2_24

TITLE: Microsoft SQL Server Management Studio
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
—————————-
ADDITIONAL INFORMATION:
The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0)
——————————
Cannot open database “JB_test” requested by the login. The login failed.
Login failed for user ‘JBAdmin’. (Microsoft SQL Server, Error: 4060)

-> I don’t have the database “JB_test” on the instance, so I was not sure why I was getting this error. Later I understood the isssue, It seems like I have created a database JB_Test before 1 hour with the same name JB_Test and dropped it and that is causing the isssue. I spoke to one of the Azure expert and he pointed out that even though we delete the database, the database will be there hidden for atleast 1 day. Instead of waiting for 1 day, I used a different name and started the import.

Blog2_25

Blog2_26

Blog2_27

Blog2_28

Blog2_29

-> It is complete and we see the database JB_TestWithData as an Azure SQL Database,

Blog2_30

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Creating a SQL database in Microsoft Azure portal

1) Open http://portal.azure.com/.

2) Start a free trial there. Put your email and password and get in. You will see
the below screen.

3) Click on “All resources”

Azure_Blog1_1

4) Click on “Add”,

Azure_Blog1_2

5) Click on “Databases” and then “SQL Database”.

Azure_Blog1_3

6) Click on “Sign Up for a new subscription” and selected as shown in the below screenshot,

Azure_Blog1_4

7) Provide details about you and get the Azure Subscription.

8) Once you have the subscription in place, Perform steps 3 to 5 again.

9) Below screen comes up once you click on “+Add”, “Databases” and then “SQL Database”. Click on “Server” and provide all details related to the server and click “Select”.

Azure_Blog1_5

10) You will see a screen like the below. Click on “Server” and in this case I have selected “Basic” instead of default “Standard” for cost reasons and Click “Apply”.

Azure_Blog1_6

11) Click on “Create” now. Wait for the deployment to complete,

Azure_Blog1_7

12) Once the deployment completes. Click on “All Resources” and you will see the “SQL serve” and the “SQL Database” as shown in the screenshot below,

Azure_Blog1_8

13) Click on the “SQL Database” and you will see the “Server name” and “connection strings”.

Azure_Blog1_9

14) Click on “Set server firewall” and add the IP addresses of machines that will be accessing the SQL server. Incase there are ipaddresses from 1.2.3.56 to 1.2.3.678, you can basically put that in START IP and END IP. Click on Save.

Azure_Blog1_10

Azure_Blog1_11

15) Open SQL server management studio and type the server name. Select “SQL server Authentication” and use the login that you created in step 9. In my case I created login “JBAdmin”.

16) Below is what I see,

Azure_Blog1_12

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.