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'

 

The 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…