How to restore Microsoft SQL database from Azure blob (with special characters in logical name)

I was struggling with this and now I found a solution I would like to share 🙂

I have setup Managed Backup on a SQL server and now I have a lot of backupfiles located in a storage account on Azure. I needed to figure out how to use these files for database restore. First I tried simply using SQL Server Management Studio, but I didn’t get very far. I can select the blob storage, but for some reason it does not show any files?

Next option was restoring using a query.

I wanted to restore the database to a new server and I want the database files to be located in another folder. To do that I need to know the logical names of all files in the database backup, you can get that by using this query…

RESTORE FILELISTONLY

FROM URL = 'https://mybackupstorage.blob.core.windows.net/mybackupcontainer/MyDatabase 2015_726ef093fa6d4165b0492221922832bb_20170614141530+02.bak'

 

SQLrestore1The result of this query lists the logical name of alle database files, and the physical location of the files on the server where the backup was taken.

Notice that the above logical names contains parentheses. I could not find a way to escape these, so instead I used variables.

DECLARE @fn1 VARCHAR(50)

DECLARE @fn2 VARCHAR(50)

SET @fn1 = 'Demo Database NAV (7-0)_data'

SET @fn2 = 'Demo Database NAV (7-0)_log'

RESTORE DATABASE [My database 2015]

FROM URL = 'https://mybackupstorage.blob.core.windows.net/mybackupcontainer/Mydatabase 2015_726ef093fa6d4165b0492221922832bb_20170614141530+02.bak'

WITH

MOVE @fn1 TO 'C:\SQL data\Mydatabase2015_Data.mdf'

,MOVE @fn2 TO 'C:\SQL data\Mydatabase2015_Log.ldf'

 

The result should hopefully be something like this…

SQLrestore2

Azure SQL Long-term backup retention

Azure SQL Long-term retention just want into public preview. I was looking for a guide to set this up, but I couldn’t find anything, so here is my own little guide 🙂

  • Log into your Azure portal (https://portal.azure.com)
  • Click on azure_sql_ltr_ss3 and search for ‘backup’. Click the result ‘Backup and Site Recovery (OMS)’ to create a new Recovery Services Vault.

azure_sql_ltr_ss5

  • Fill out the form. Select the subscription that also contains your SQL server
  • Select the Resource group that your SQL server is also a member of
  • Select your preferred Location
  • Click Create
  • Wait a few seconds while your Recovery Services vault is being created
  • Go into SQL Servers
  • Click on your SQL server
  • Click on the new setting called “Long-term backup retention”

azure_sql_ltr_ss1

  • If you have not yet accepted the preview terms you will see this message:

azure_sql_ltr_ss2

  • Click the message, click the checkbox that shows up, and click the OK button
  • Select the databases you would like to backup
  • Click the Configure button on top of the pane
  • Select your Recovery service vault
  • Create a new retention policy by filling out the name and choosing the retention period
  • Click OK

azure_sql_ltr_ss6

  • Click the Save button on top of the pane

azure_sql_ltr_ss7

  • That should be it 🙂

Windows Server 2012 R2 loosing network connectivity

Just a short post before I forget this myself. I have been struggling with two Hyper-V host servers loosing network connectivity every now and then. I have 3 servers in totalt, one of them never had this problem. The server simply stops communicating on all network interfaces at the same time. It’s still connected, but I can’t ping the default gateway. I have been through every setting multiple times, but finally I found this one setting on the network card properties that was different from the server that was working. Under the network card properties choose Configure… and then click the Advanced tab. Find the Virtual Machine Queues property and set it to disabled. Haven’t had any problems after this change. As you can see from the screenshot we’re running Broadcom NetXtreme Gigabit Ethernet network interface cards and the servers are Dell PowerEdge R620, don’t know if it matters? Also tried updating drivers and firmware a couple of times, but without any luck.

VMQ

ClickOnce – Automatically update version

I often have to update Dynamics NAV ClickOnce applications and I got tired of updating the version in the two manifest files, that is why I updated my batch file to do it for me.

I keep my ClickOnce application files in c:\inetpub\wwwroot\xxx. When I need to make changes I edit or add files to that folder. When any change are made to the ClickOnce application files you need to update the manifest files using mage.exe. To do that I use this batch file (run as administrator)

SET /p VERSION=<c:\clickonce\NAV2016Demo.version.txt
SET /A VERSION=%VERSION%+1
>c:\clickonce\NAV2016Demo.version.txt ECHO %VERSION%
SET VERSION=9.0.0.%VERSION%
ECHO %VERSION%
CD C:\inetpub\wwwroot\ClickOnce\NAV2016demo\Deployment\ApplicationFiles
DEL /Q web.config
c:\clickonce\mage.exe -update Microsoft.Dynamics.Nav.Client.exe.manifest -FromDirectory . -version %VERSION%
CD C:\inetpub\wwwroot\ClickOnce\NAV2016demo\Deployment
c:\clickonce\mage.exe -update Microsoft.Dynamics.Nav.Client.application -appmanifest ApplicationFiles\Microsoft.Dynamics.Nav.Client.exe.manifest -appcodebase https://2016install01.domain.dk/NAV2016demo/Deployment/ApplicationFiles/Microsoft.Dynamics.Nav.Client.exe.manifest -version %VERSION% -minVersion %VERSION%
COPY c:\clickonce\web.config C:\inetpub\wwwroot\ClickOnce\NAV2016demo\Deployment\ApplicationFiles\
PAUSE

This will update the manifest files and increment the version and minimum required version by 1.

Please note that the current version is read from the file C:\ClickOnce\NAV2016Demo.version.txt file and not directly from the manifest files (too complicated for me ;-))

Also note that the version will be set to 9.0.0.x where x is the number that is incremented. You can of course modify this any way you like.

Adding BGInfo extension on Azure Resource Manager VM

Microsoft has not yet added a way to add extensions to Resource Manger VMs on Azure. Luckily there is a way to do this using Powershell.

Prerequisites:

You need to have the Azure Powershell tools installed – see https://azure.microsoft.com/da-dk/documentation/articles/powershell-install-configure/

Afterwards connect to Azure – see this http://azurefabric.com/powershell-1-0-and-arm-tenants-and-subscriptions/

First step:

First we need to install the Windows Azure VM Agent on the VM server. This can be downloaded from here – http://aka.ms/vmagentwin.

You need to run this installer from an elevated command prompt (run command prompt or Powershell as administrator).

If you skip this step you will be in all kinds of trouble 😉 I didn’t install this before I installed the BGInfo extensions. The extension was installed, but the status was failed. Also the standard extension (if you installed diagnostics) Microsoft.Insights.VMDiagnosticsSettings failed after this. Installing the Azure VM Agent afterwards does not solve these problems. I’m currently trying to uninstall the BGInfo extension again from the portal, it has been running for 54 minutes now… I will update this post later, when I have a solution for this mess.

Second step:

Start up your Azure Powershell and connect to your subscription (see prerequisites)

Run this command to install the BGInfo extension (thank you BigSkyTech – Technet forums post)

Warning! Users will get disconnected while the VM is being updated!

Set-AzureRmVMExtension -ExtensionName BGInfo -Publisher Microsoft.Compute -Version 2.1 -ExtensionType BGInfo -Location northeurope -ResourceGroupName YOUR_RESOURCE_GROUP -VMName YOUR_VM_NAME

Next time you log in to your server, the BGInfo should now be displayed, yay!

The extension also becomes visible in the Azure management portal – look under Virtual Machines > YOUR_VM > Settings > Extensions

Want more?

Wanna see all available extensions? Run this…

Get-AzureVMAvailableExtension | Out-GridView

If you want to install Microsoft Antimalware you can run…
Don’t use this just yet… it’s kinda buggy 😦
Set-AzureRmVMExtension -ExtensionName IaaSAntimalware -Publisher Microsoft.Azure.Security -Version 1.3 -ExtensionType IaaSAntimalware -Location northeurope -ResourceGroupName YOUR_RESOURCE_GROUP -VMName YOUR_VM_NAME
Be patient, it can take quite a while to add certain extensions, don’t give up! 🙂

 

 

sessionState mode=”InProc”

Just wanted to share a silly mistake that I made when testing NAV 2016 web client (could have been any version).

NAV 2016 webservice error

Problem:

When trying to access the webclient I got the above error (<sessionState mode=”InProc”/>).

Solution:

I forgot to add the port number in the url. The webclient runs on port 8080 (default), but I didn’t specify this when typing in the url. I was trying to access https://nav2016.yourdomain.com/INSTANCE_NAME/WebClient/ instead of https://nav2016.yourdomain.com:8080/INSTANCE_NAME/WebClient/.

The reason this has given me such a headache is that the url https://nav2016.yourdomain.com/INSTANCE_NAME/WebClient/ does exist, but the web client is configured for port 8080 which is why I am getting the above error.

I hope this will help someone else save some time 🙂