Azure – Backup and restore SQL DB using SSMS

Reading Time: 3 minutes

A quick post talking about how to backup and restore a SQL database on Azure using SQL Server Management Studio (SSMS).

First, you will need to install SSMS. You can download it here: https://go.microsoft.com/fwlink/?linkid=875802

Once installed, in order to access the database, you will need the server name where is installed. So, you will have to check the Server name in Azure Portal (you can also do it by Powershell of course):

Now, open SSMS and access the server name (you gathered the information before):

Export/Backup Database

Once you logged in, select the database you want to export -> Export Data-tier Application

In the new window > Next > Select where do you want to save the DB (you can do it locally or in a Storage Account), in our case Local Disk:

In the Advanced tab you can choose which tables you want to export, we will Select All:

Finally, we have a Summary of the process before exporting the database:

Then it will start to export the database, depending on the size of your DB will take more or less time to export:

Finally, we will have a file with .bacpac extension.

Import/Restore database

The process is almost the same but now we select Import Data-tier Application:

Continue selecting the file with .bacpac extension we exported before:

Then, with Database settings, here you can choose different options as you can do on the Azure portal:

Summary of the imported database:

Finally, it was imported successfully (it took a while for a 10 MB DB):

In consequence of the restore, it will appear the restored database (Restore_DB) in SSMS:

 

Therefore, I posted a quick way to export and import a SQL database by using SSMS. You could use it as a backup (please, not in local) or for example, to overwrite changes from UAT to PROD.

 

Azure – Error when adding new rule on NSG

Reading Time: 2 minutes

Hello everyone!

It’s been a while since I wrote something, but I was so busy with other things (University) and I wasn’t able to allocate time to write anything.

Today I’m going to talk about an issue I found on Azure when trying to add new rules to some NSGs.

To create rules in Azure I used this script from TechNet Gallery: https://gallery.technet.microsoft.com/scriptcenter/Create-Azure-Network-5f5c5332

Problem

I was trying to add some rules in an NSG with the address 193.23.120.230/30 and, when I execute the code, the output was:

Set-AzureRmNetworkSecurityGroup : Security rule has invalid Address prefix. Value provided: 193.23.120.230/30.
StatusCode: 400
ReasonPhrase: Bad Request
OperationID : ‘b2f7-b2f7-b2f7’
At line:4 char:55
+ … efix 193.23.120.230/30 -SourcePortRange * | Set-AzureRmNetworkSecurityGroup
+                                           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : CloseError: (:) [Set-AzureRmNetworkSecurityGroup], NetworkCloudException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.Network.SetAzureNetworkSecurityGroupCommand

Tried again and again, and this only happened in certain NSGs, so well I thought there was a problem with the command line, I tried the same with the GUI and… the same.

Solution

Investigating the error it didn’t match with any of the new rules I was trying to add. Address 193.23.120.230/30 seems correct but if we use a subnet calculator, you will see this:

The right address should be deleting all ones after the netmask because it doesn’t care about what is after the netmask bits.

This means that the new address is 193.23.120.228/30 because we put zeros instead of ones after the netmask bits.

So, it seems a CIDN error! Seems that Azure let me add this rules in the past, but now it’s not accepting it so, if we change it the way that subnet calculator does, problem resolved!

Solution? Had to delete the non-compliant CIDR rules and added the new ones CIDR compliant. Executed the same in other NSGs and worked like a charm.
All rules are finally shown in Azure Panel:

Well, seems that Azure didn’t comply with CIDR addresses in the past and now it’s mandatory if it founds any non-compliant CIDR rule. An easy mistake that we can avoid checking our addresses before we try to add them to Azure.

And that’s all, Happy New Year!