Import-NAVData into an empty database

I have been struggling with this a few times, and I was sure that I succeeded earlier when trying to import a .navdata file into an empty SQL database, so this time I’m going to write a short post about it.

Looks like it is really quite simple.

If you have a .navdata file that contains application, application data, global data and some company data as well, then you can import this with PowerShell directly into an empty SQL Server database.

First you need to create the empty database and you can do this from Microsoft SQL Server Management Studio or the Azure Portal if you are on Azure SQL.

Then open up PowerShell and import your NAV-modules. I use the Import-NAVModules cmdlet from Cloud.Ready.Software.NAV, but you can also use…

Import-module “C:\Program Files (x86)\Microsoft Dynamics NAV\11.0.20783\Service\Microsoft.Dynamics.Nav.Management.dll”

Then run Import-NAVData…

Import-NAVData -DatabaseServer <SQL server name> -DatabaseName <Database name> -ApplicationDatabaseServer <SQL server name> -ApplicationDatabaseName <Database name> -FilePath “C:\temp\backup.navdata” -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -CommitPerTable -Force

Note this example is a single tenant database. If you are using SQL server instances you can type mysqlserver\myinstance under -DatabaseServer and -AppliationDatabaseServer.

Does it work on Azure SQL? Yes it does…

Import-NAVData -DatabaseServer $azuresql -DatabaseName $azuresqldb -DatabaseCredentials $azuresqlcred -ApplicationDatabaseServer $azuresql -ApplicationDatabaseName $azuresqldb -ApplicationDatabaseCredentials $azuresqlcred -FilePath “C:\temp\backup.navdata” -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -CommitPerTable -Force

If you omit the -Force parameter you will get this…

Import-NAVData : System table $ndo$dbproperty is missing or database MyDatabase is not a Dynamics NAV application database
.
At line:1 char:1
+ Import-NAVData -DatabaseServer $azuresql -DatabaseName $azuresqldb -D …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (0:Int32) [Import-NAVData], NavSqlException
+ FullyQualifiedErrorId : agidonsql2.database.windows.net\nhtest2,Microsoft.Dynamics.Nav.Management.Cmdlets.Import
NavData

EDIT: I noticed that the actual company data was not imported with the above commands, not sure why. But if you experience the same you can always run the commands again without the -IncludeApplication, -IncludeApplicationData and -IncludeGlobalData parameters.

Also I got this message on Azure, when the Import-NAVData command finished…

Import-NAVData : Error while trying to set the collation for database nhtest2 containing tenant default. The error was:
The following SQL error was unexpected:
The database could not be exclusively locked to perform the operation.
ALTER DATABASE failed. The default collation of database ‘nhtest2’ cannot be set to Danish_Greenlandic_100_CS_AS.

I tried deleting the database and recreating it from the Azure Portal specifying ‘Danish_Greenlandic_100_CS_AS’ as collation. After that I was able to restore everything to the Azure SQL database.

 

Advertisements

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