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.

Leave a Reply