[Step by Step] Restore a Database from Azure Blob to Azure SQL Server
In one of the requirements, we had to move a Database uploaded to ‘Azure Blob Storage’ to Azure SQL Server.
If you got a question, why we need to move SQL Database file from Azure Blob to Azure SQL Server, like me, below is a sample scenario
- Customer IT team, uploads their Database file to Azure Blob storage every week using AZcopy
- To consume the Data, we either have to restore the Database file to Azure SQL Server or to your local SQL server.
Below are the steps to restore Database file from Azure Blob to Azure SQL Server.
Prerequisites:
- Azure Subscription
- Create a ‘Storage Account’ with ‘Blob’. Refer my previous article for steps to create
- Database file uploaded in Azure Blob Storage
- SQL Server Management Studio (SSMS), as I am going to use this tool in next steps.
Steps to restore Database from Blob to Azure SQL Server:
- Connect to ‘Azure SQL Server’ using SQL Server Management Studio (SSMS)
- Right click on ‘Databases’ and choose ‘Import Data-tier Application…’
- In ‘Import Settings’ tab of the ‘Import Data-tier Application’ window
- Select ‘Import from Windows Azure’
- Click ‘Connect…’
- Provide ‘Azure Storage account’ name
- Account Key
- Click ‘Connect’
- In the next window, pick the Azure Database back up file upload in the Blob and click ‘OK’
- In ‘Database Settings’ tab, provide the ‘New database name’ and click ‘Next’
- That’s it, now the Restore process should start with ‘Progress’ window.
- Give it some time and once the process completed, you will see ‘Success’ Status
🙂
SqlError: The backup set holds a backup of a database other than the existing database
I got below error, when I was trying restore a database from Azure Blob Storage to my local SQL server using ‘SQL Server Management Studio (SSMS)’.
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘{DB_Name}’ database. (Microsoft.SqlServer.SmoExtended)
Reason:
- I created a new Database (DB) first and tried to restore the DB from Azure blob to this new DB.
Fix:
Below approach solved the issue
- Don’t create a new Database before hand.
- Right click ‘Databases’ and select ‘Restore Database’
- Pick your ‘Source’ Database
- In the ‘Destination’ section, provide a new Database name as highlighted below
- Click ‘OK’ to complete the Restore process.
If you want to create a Blank Database first and restore.
- Set below settings in ‘Options’ tab of ‘Restore Database’ window
- Pick existing Database
- Click ‘OK’ to complete the Restore process.
🙂
[Step by Step] Connecting to Azure SQL Server using OLEDB Connection from SSIS
Connecting to Azure SQL Server from SSIS using ‘OLEDB Connection manager’ is not a straight forward way, if you are to connect using your ‘Azure Account’ credentials.
The only way to connect using ‘Azure Account’ is by using ‘ADO.NET connection’ manager.
But then, how to connect to Azure SQL using OLEDB? Answer is by using ‘SQL User’. And yes, we need to create ‘SQL User’ on the Azure DB which you are connecting to and use the same in SSIS OLEDB.
Below are the steps to create ‘SQL User’ and use that to connect to ‘Azure SQL Server’ from SSIS OLEDB.
Create a ‘SQL Login’ on Azure SQL Server:
- Connect to Azure SQL Server using SQL Server Management Studio (SSMS) using the Azure Account. ‘Azure Account’ must be Administrator to ‘Azure SQL Server’
- Select ‘Master’ Database and open a ‘New Query’ window.
- Note that, ‘SQL Login’ creation query must only run on ‘Master’ database
- Create a ‘Login Account’ using below query. In my query I am creating a Login Account by name ‘MyLogin’
Create a ‘SQL User’ on required Databases:
- Post creation of ‘SQL Login’, now we have to create ‘SQL User’ account against all the Databases, which you want access from SSIS OLEDB.
- From the SSMS -> Object Explorer, select the Database and open a ‘New Query’ window.
- Create a ‘SQL User’ using below query. In my query I am creating a SQL User by name ‘MyUser’
- Add ‘db_datareader’ and ‘db_datawriter’ roles to the ‘MyLogin’ Login Account using below queries.
- Note: You can combine and run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries together.
- Also, run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries against ‘Master’ Database as well.
- Refer this article for list of ‘Roles’
Connect to Azure SQL from SSIS OLEDB:
As we have ‘SQL User’ created and granted access against the required databases, to connect to Azure SQL from OLEDB.
- Open the ‘OLEDB Connection Manager’
- Set ‘Server name’ to ‘Azure SQL Server’ name
- Set ‘Authentication’ to ‘SQL Server Authentication’
- Set ‘User name’ and ‘Password’ to ‘SQL User’ credentials created in above section
🙂
PowerApps – Create an ‘Account’ in Dynamics 365 from App using Flow
If you are new to PowerApps, please go through my previous blog
In this post, lets build an App with an Account Form and create the record in Dynamics 365.
Prerequisite:
- You must have a PowerApps account. Refer blog for details.
- Create a Microsoft Flow with logic to create an Account record in Dynamics 365
- Build an App and trigger Flow
Steps to create Flow:
- Login to PowerApps Portal
- Navigate to Business Logic -> Flows -> Create from blank
- In the new Flow screen, Choose Action, select ‘Dynamics 365’
- Select ‘Create a new record’ from Actions.
- Now we need to put place holders in the fields which we pass data from PowerApp.
- To add a place holder to ‘Account Name’ field, select the field, in ‘Dynamics content’ window, click on ‘Ask in PowerApps‘ button.
- A new place holder will get added to ‘Account Name’ field.
- I repeated the above step for ‘Main Phone’ and ‘Description’ fields.
- Rename the Flow to CreateAccount and Save
Invoke Flow from PowerApp:
- To know, how to create a new App and add controls to form, refer my earlier blog
- To trigger the Flow, select the ‘Create’ button, go to ‘Action -> Flows’
- From the ‘Data’ window, select the flow created in above section (i.e.,CreateAccount)
- On ‘OnSelect’ event of button, trigger the Flow ‘CreateAccount’ by calling ‘Run’ method and passing the 3 placeholder parameters.
- CreateAccount.Run(txtName.Text,txtDesc.Text,txtMobile.Text)
- Thats it, now its time to test the App
Test the App:
- Press F5
- Set the values and click ‘Create’
- Open the D365 App and you should see new ‘Account’ record.
Refer my earlier article to build an App using Excel as Data Source.
🙂
PowerApps – Error while connecting to Excel Data Source
If you are new to PowerApps, please go through my previous blog
In my last Post building, I explained the steps to build an App using Excel file as Data Source.
While I was exploring more with that App, I encountered an error when playing with different excel files.
Reason & Fix:
- If you have formulas defined in the Excel file, PowerApp will throw error as the PowerApps are not supporting Excel containing Formulas at this point.
- Fix was, I had to delete Formulas and keep only data in the excel file to run the App.
🙂
PowerApps – Build an App using Excel data as Source
If you are new to PowerApps, please go through my previous blog
In this post, I am going to walk you through building an App using Excel as ‘Data Source’.
I am going to build an App by
- Prepare an Excel file with data formatted as ‘Table’
- Place it in ‘One Drive For Business’.
Pre-requisites:
- You must have a PowerApps account. Refer blog for details.
- An Excel sheet with data formatted as ‘Table’.
- ‘One Drive For Business’ account
- This comes free with Dynamics 365 30 days trail.
Steps to configure Excel:
- Create an Excel file with some data. I prepared ‘Customer’ records with 4 columns.
- You should format this data as ‘Table’ as the PowerApp, only consider Tables, which you will notice in next sections.
- Provide name to the Table; We will use this Table name in ‘Power App’. I named my table as ‘Customers‘.
- Save the Excel
Steps to upload Excel to OneDrive:
- Login to http://portal.office.com
- Connect to ‘OneDrive’ and upload the Excel File
Steps to create App:
- Login to PowerApps Portal
- Create a New App with ‘OneDrive for Business’ template, as I uploaded the Excel to OneDrive.
- Note: Its not necessary that you should pick ‘OneDrive for Business’ template, you can even create a ‘Blank app’ and create ‘OneDrive’ connection.
- Click ‘Create’ to establish App’s connection with ‘OneDrive For Business’.
- Choose the Excel file
- Select the ‘Table’ and click ‘Connect’; This table ‘Customers‘ is nothing but the table name we given in ‘Excel sheet’.
- Now you would experience the power of ‘Power Apps’ as it instantly come up with UI using the excel data. App auto create 3 screens for Browse Screen,Details Screen and Edit Screen.
- You can reorder the columns by selecting ‘Data’ or you can change the ‘Sort Column’ if you want.
- Finally, press F5 to run the App.
Refer my previous blog to build a simple calculator App.
🙂
PowerApps – Step By Step – Build a simple Calculator App
In my previous article I detailed the steps to get started with PowerApps by signing up to the PowerApps portal.
Now, let me walk you through the steps to create and publish a simple ‘Calculator’ App.
The Calculator App will have 2 Text boxes and an ‘Operation’ dropdown. Upon clicking the ‘Calculate’ button, based on the Operation we either Sum or Subtract the values and show the result.
Pre-requisites:
- You must have PowerApps account. Refer my previous article
Steps to build ‘Calculator’ App UI:
- Connect to the PowerApp studio and click on ‘New App’ of Mode ‘Canvas’
- From the ribbon, click on ‘Insert’ tab and drag & drop the controls on to the Canvas
- For the ‘Text Input’ controls, select ‘Format’ as ‘Number’ so that it only allow digits.
- Add the ‘Dropdown’ control to the Screen
- To add “Sum”,”Substract” as options to the ‘Dropdown’, select ‘Items‘ property and set [“Sum”,”Substract”]
- Add the ‘Button’ control to the screen and set the text as “Calculate”
- Add 3 labels
- Label ‘A’, Label ‘B’ and Label ‘Result’
Steps to add the business logic:
- On buttons ‘OnSelect’ event,
- Declare a variable “varResult”
- Read the selected operation (i.e., Sum/Subtract) from the Dropdown
- Write an If/Else statement
- Add TextA, TextB; if the dropdown values is ‘Sum’ If(ddlOperation.Selected.Value=”Sum”,txtA.Text+txtB.Text))
- Subtract TextA, TextB; if the dropdown values is ‘Substract’ If(ddlOperation.Selected.Value=”Substract”,txtA.Text–txtB.Text))
- Set the Sum/Subtract value to the “varResult” variable using ‘Set‘ method
- Finally, we should display the result by setting it to ‘Result’ label. So, set ‘Text’ as ‘varResult’
Time to Test the App:
- Press F5 to test the App
Save and Publish the App:
Once you done with testing, its time to Save and Publish the App.
- To save the App, either press (Ctrl+S) or go to File -> Save
- Choose either ‘The Cloud’ option or ‘This computer’
- ‘Cloud’ option would save the App in your ‘PowerApps’ account
- ‘Computer’ option would provide the App in .zip folder to save locally.
- Post save, to make the App available to Users, click on ‘Publish’
Notes:
- PowerApps provides excel like formulas to apply business rules. Refer this blog to get exhaustive list of formulas.
🙂
Badges
