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.

Advertisements

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.