-> 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,

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,

-> Click on next,

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

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

-> Specify the dacpac file,

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

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,

-> Click on next,

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

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

-> Specify the bacpac file,

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

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.

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

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.