Exporting and Importing Data

1. Morten Jensen Sr. Program Manager June 2014 Design Insights Microsoft Dynamics NAV 2013 R2 Exporting and Importing Data

2. What’sNew • Microsoft Dynamics NAV 2013 R2

3. Challenges to address Some partners master Windows PowerShell, but others don’t Partners and customers need to move some of the data, not always complete databases, between systems Customer wants to train a new employee on real data, but wants to take a full copy of the companydata into a sandbox environment Partner needs to take customer production data and application “back home” for deep debugging Replacement of FBK from previous versions

4. Challenges for Sean – the Power User • Exportcompany datain a familiar user interface with the intent to import it in another database for training purposes. • Import company data • in an easy way into a training database. • Export application and datain order to pass it to the Microsoft Dynamics NAV partner for offline debugging.

5. Challenges for Viktor – the VAR Developer • Import an application and data • into his sandbox environment in order to investigate a complex bug offline. • Export company data from an unmounted tenantbecause the tenant cannot be mounted, the client cannot be used for export.

6. What we need….

7. Exporting and Importing Data • In Microsoft Dynamics NAV 2013 R2 CU 8 • … partners and super-users can export and import company data and application data using Windows PowerShell cmdlets or the Microsoft Dynamics NAV clients to move data between systems. • This replaces the ”FBK export/import” feature that was available in earlier versions.

8. How it works? • User interface • Power users can export and import using familiar user interfaces to complete common scenarios in the Microsoft Dynamics NAV Windows client and the Microsoft Dynamics NAV Web client. • Windows PowerShell • IT professionals and developers can use Windows PowerShell to complete advanced scenarios. • Determine what to export and import • You can export and import parts of a Microsoft Dynamics NAV solution with a focus on companies. For example, you can include business data, data common to all companies, application tables, and application objects.

9. How it looks?

10. Sean: Exportcompany data • Open the Microsoft Dynamics NAV Windows client. • In the Search box, enter Export to a Data File, and then choose the related link - or choose Departments, Administration, IT Administration, General. • In the Export to a Data File page, enter the following information: • A description of the data to export (optional), such as My first data export. • Specify the company to export, such as CRONUS International Ltd. • Deselect the four checkmarks: All Companies, Global Data, Application Data, and Application (optional) • Choose the OK button • In the Export Data file dialog, specify the name and location of the file that will be created, such as e.g. c:\MyExport.navdata • Choose the Save button and wait a moment for the operation to complete. • If Sean wants to pass application and data to the partner for offline debugging, he chooses all four checkmarks to include the application in the export file.

11. Sean: Import companydata • Open the Microsoft Dynamics NAV Windows client. • In the Search box, enter Import from Data File, and then choose the related link - or choose Departments, Administration, IT Administration, General. • In the File Name field, choose […] to open the file browser, choose the file to import, and then choose Open. • Back in the Import from Data File page make a note of: • The description of what the file contains, such as ”My first data export”. • The date of the export. • The tenant ID. • Depending on what was originally exported to the file, select one or more of the following fields: • All companies – an easy way to import all companies in the file. Alternatively, choose the individual companies in the list. • Global Data – cross-company data. • Application Data – data specific to this application. Note: The application itself cannot be imported in the Microsoft Dynamics NAV Windows client. • Choose the OK button, and wait a moment for the operation to complete.

12. Viktor: Import complete application+data • Open the Microsoft Dynamics NAV Administration Shell.You must have stopped the Microsoft Dynamics NAV Server service. • To see the cmdlets to use, type this at the prompt: • Get-Help NAVData • Output: • Export-NAVData – export data from Microsoft Dynamics NAV • Import-NAVData – import data to Microsoft Dynamics NAV • Get-NAVDataFile – list content of a Microsoft Dynamics NAV data file (*.navdata) • To see what is in the file, type • Get-NAVDataFile –FileName c:\MyExport.navdata • Output (see picture): • To import the application and all data, create an empty database using SQL Server, and type the following: • Import-NAVData-DatabaseServer 'MyServer' • -DatabaseName 'Demo Database NAV (7-1)' • -IncludeGlobalData –AllCompanies -ApplicationData -Application • -FileName c:\MyExport.navdata • Oncecompleted, the database is ready to use.

13. Viktor: Export from ‘un-mountable’ tenant • If a tenant cannot be mounted, you can export its data for investigation. • Open the Microsoft Dynamics NAV Administration Shell.You must have stopped the Microsoft Dynamics NAV Server service. • To see different parameter profiles for Export-NAVDatacmdlet, type this at the prompt: • Get-Help Export-NAVData • Output (partial): • SYNTAX • Export-NAVData [-DatabaseServer] <string> [-DatabaseName] <string> [[-ApplicationDatabaseServer] <string>] • [-FileName] <string> [[-ApplicationDatabase] <string>] [-AllCompanies] <switch> • [[-IncludeApplication] <switch>] [[-IncludeApplicationData] <switch>] [[-IncludeGlobalData] <switch>] • [[-Description] <string>] • This variant accesses the SQL Server database directly without accessing Microsoft Dynamics NAV Server. • To export, type: • Export-NAVData -DatabaseServer 'MyServer' • -DatabaseName 'Demo Database NAV (7-1)' • -AllCompanies -FileName c:\file\my.navdata

14. Viktor: Programming in C/AL code • New C/AL functionsintroduced: • EXPORTDATA • IMPORTDATA • DATAFILEINFORMATION

15. Exportusing the Windows Client

16. Import using the Windows Client

17. Export using Windows PowerShell # Export all companies to a .navdatafile Export-NAVData-ServerInstanceProductionServer` -AllCompanies-FileNameMyFile.navdata # Export data from all companies from all tenants a .navdatafile Get-NAVTenant-ServerInstanceProductionServer| SelectServerInstance,Id| Export-NAVData-AllCompanies-FileNameMyFile.navdata # Export data from all companies from all tenants to separate .navdata files – use tenant ID as the file name Get-NAVTenant-ServerInstanceProductionServer| SelectServerInstance,Id,@{Name='FileName';Expression='Id'} | Export-NAVData-AllCompanies

18. Import using Windows PowerShell # Import a named company and global data from a .navdatafile Import-NavData-ServerInstanceProductionServer` -Company'CRONUS International Ltd.‘-IncludeGlobalData ` -FileNameMyFile.navdata # Import all companies from the production environment into a test environment Export-NAVData-ServerInstanceProductionServer` -AllCompanies-FileNameMyFile.navdata| Import-NavData-ServerInstanceTestServer-AllCompanies

19. Security • Pages are protected by the Microsoft Dynamics NAV permission system • Operations are protected by new System permissions: Export Data to Data file (5410) • Import Data from Data file (5420) • Setup • Open the Permission Sets page. • Create new permission set, such as DATA-EXPORTIMPORT. • Add System permissions 5410, Export Data to Data File, and 5420, Import Data from Data File. Also add any additional permissions that you find relevant. • Assign this permission set to the relevant users.

20. What it is not • It’s NOT a backup strategy • Use SQL Server backup instead. • It’s NOT a cross-version data carrier • No compatibility between versions of Microsoft Dynamics NAV. • It’s NOT a .FOB file alternative • A .navdata file cannot be imported as a .FOB file similar to how you could import .FBK files in earlier versions of Microsoft Dynamics NAV.

21. What it does not • It does NOT guarantee data consistency • There is no data validation during export or import. We recommend single-user mode. • It does NOT encrypt or password-protect the data file • The .navdata file is not encrypted or password protected; use external tools to encrypt and password protect as needed. • It does NOT detect in-transfer file corruption • No protection against errors introduced during storage or transmission. • It does NOT compress data in the output file • The .navdata file is not compressed; use an relevant compression tool if file sizes are an issue.

22. Benefits • Flexibilityon the data that you export and import. • Ease of usefor non-technical power users to export or import data in a familiar user interface. • Powerfulfor the more advanced IT professional using scripting. • On parwith previous versions and well-known use cases.