The Complete Guide to Archiving NAV 2016 Data with SQL and BCP

Posted on by

The Call Nobody Wants to Get

It was one of those requests that sounds simple on the surface: “We need to archive the old Navision data before we shut it down.”

The client had been running Dynamics NAV 2016 for years. GL entries, purchase invoices, sales history, inventory movements — all of it sitting in a SQL Server database that was about to be decommissioned. They had already migrated to a newer system, and the old NAV server was just costing money to keep alive.

The ask was clear: extract the key historical data into a format that is accessible without NAV, store it somewhere safe, and then pull the plug on the server.

Simple enough in theory. But as usual, the devil is in the details.

The Environment

  • System: Microsoft Dynamics NAV 2016 (version 9.0, also known as Dynamics NAV 2016 W1)
  • SQL Server: SQL Server 2012, on-premises
  • Database size: ~88 GB
  • Goal: Export the critical transactional and master data tables to CSV before shutdown

The first thing I did — before anything else — was take a full SQL Server backup (.bak). I cannot stress this enough: no matter how good your export script is, the .bak is your safety net. If something goes sideways months later during an audit, you want to be able to restore the full database and query it directly.

With the backup safely stored, I moved on to the structured CSV exports.

The NAV 2016 SQL Table Naming Convention

If you have never worked directly with NAV’s SQL database, here is the key thing you need to know:

NAV stores all company data with a prefix in the table name, using the company name followed by a dollar sign: Company Name$Table Name

So the G/L Entry table for a company called “Contoso Ltd” becomes:

Contoso Ltd$G_L Entry

Notice a second rule: dots (.) and slashes (/) in NAV table names become underscores (_) in SQL. So G/L Entry becomes G_L Entry, Cust. Ledger Entry becomes Cust_ Ledger Entry, and so on.

This matters a lot when you are writing export scripts, because the names you see in the NAV client are not what SQL Server has stored.

My First Attempt: PowerShell + BCP

My first instinct was to write a PowerShell script that would auto-detect the company name from sys.tables and then loop through a list of target tables, exporting each one using BCP (Bulk Copy Program — the fastest native SQL Server export tool for large datasets).

BCP has one huge advantage over everything else: no row limit. Excel caps at ~1 million rows per sheet, but tables like G_L Entry or Value Entry can have tens of millions of records. BCP handles that without blinking.

The script worked logically, but I kept hitting a wall with how PowerShell handles System.Data.DataTable row indexing. The auto-detection query would return results, but accessing $row["name"] was throwing null reference errors on certain rows. I went through six iterations trying to fix it — different ways to iterate the DataTable, parameterized queries, you name it.

At some point I just stopped and asked myself: why am I doing this in PowerShell when I have SSMS open right in front of me?

The Solution: Pure T-SQL with xp_cmdshell

The cleaner approach was to do everything inside SQL Server itself, using xp_cmdshell to invoke BCP directly from a T-SQL script. No PowerShell, no external dependencies, no DataTable headaches.

Here is the full script. It:

  1. Enables xp_cmdshell temporarily
  2. Creates the output folder structure on disk
  3. Loops through all 46 target tables using a WHILE loop
  4. Runs BCP for each table via xp_cmdshell
  5. Disables xp_cmdshell when done

Just open it in SSMS, hit F5, and watch the Messages tab.

Security note: The script below uses SQL Authentication (-U and -P) for clarity. If your environment supports it, prefer Windows Authentication by replacing the -U and -P flags with -T (Trusted Connection). This avoids storing credentials in plain text, which matters especially if the script ends up saved alongside the exported data.

-- =============================================================================
-- Export-NAV2016-Historical-Tables.sql
-- Exports 46 critical NAV 2016 tables to CSV using BCP via xp_cmdshell.
-- Run in SSMS connected to your NAV SQL Server instance.
--
-- BEFORE RUNNING:
--   1. Replace 'YourCompanyName' with your actual NAV company name
--   2. Replace 'YOUR_NAV_DB' with your database name
--   3. Replace 'YOUR_SERVER', 'YOUR_SQL_USER', 'YOUR_SQL_PASSWORD' accordingly
--      (or use -T for Windows Authentication instead of -U/-P)
--   4. Adjust @Out to your desired output folder path
-- =============================================================================

USE [YOUR_NAV_DB]
GO
SET NOCOUNT ON;

DECLARE @Out  NVARCHAR(500) = 'C:\NAV_Archive'        -- Output folder
DECLARE @DB   NVARCHAR(100) = 'YOUR_NAV_DB'           -- Database name
DECLARE @Srv  NVARCHAR(100) = 'YOUR_SERVER'           -- SQL Server instance
DECLARE @Usr  NVARCHAR(100) = 'YOUR_SQL_USER'         -- SQL login
DECLARE @Pwd  NVARCHAR(100) = 'YOUR_SQL_PASSWORD'     -- SQL password

-- Company name exactly as it appears in SQL sys.tables (before the $ sign)
-- Example: 'Contoso Ltd'  ->  tables will be  [Contoso Ltd$G_L Entry]
DECLARE @Company NVARCHAR(200) = 'YourCompanyName'

-- =============================================================================
-- Enable xp_cmdshell
-- =============================================================================
EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell',           1; RECONFIGURE WITH OVERRIDE;
PRINT 'xp_cmdshell enabled.'

-- =============================================================================
-- Create output folders
-- =============================================================================
DECLARE @mk NVARCHAR(800) =
    'mkdir "' + @Out + '" 2>nul & ' +
    'mkdir "' + @Out + '\GL" 2>nul & ' +
    'mkdir "' + @Out + '\Sales" 2>nul & ' +
    'mkdir "' + @Out + '\Purch" 2>nul & ' +
    'mkdir "' + @Out + '\Inv" 2>nul & ' +
    'mkdir "' + @Out + '\WH" 2>nul & ' +
    'mkdir "' + @Out + '\Master" 2>nul'
EXEC xp_cmdshell @mk, NO_OUTPUT
PRINT 'Output folders created at: ' + @Out

-- =============================================================================
-- Target table list
-- NAV SQL naming rule: dots and slashes in table names become underscores (_)
-- Full table name in SQL = 'Company Name$Table_Name'
-- =============================================================================
IF OBJECT_ID('tempdb..#Tables') IS NOT NULL DROP TABLE #Tables
CREATE TABLE #Tables (
    ID        INT IDENTITY(1,1),
    SqlSuffix NVARCHAR(500),   -- table name after 'Company$'
    FileName  NVARCHAR(200),   -- output CSV file name (no extension)
    SubFolder NVARCHAR(50)     -- output subfolder
)

-- GL / General Ledger
INSERT INTO #Tables VALUES ('G_L Entry',                  'GL_01_GLEntry',                 'GL')
INSERT INTO #Tables VALUES ('G_L Register',               'GL_02_GLRegister',              'GL')
INSERT INTO #Tables VALUES ('G_L Account',                'GL_03_GLAccount',               'GL')
INSERT INTO #Tables VALUES ('G_L Budget Entry',           'GL_04_GLBudgetEntry',           'GL')
INSERT INTO #Tables VALUES ('VAT Entry',                  'GL_05_VATEntry',                'GL')
INSERT INTO #Tables VALUES ('Bank Account Ledger Entry',  'GL_06_BankAccLedgerEntry',      'GL')
INSERT INTO #Tables VALUES ('Bank Account',               'GL_07_BankAccount',             'GL')
INSERT INTO #Tables VALUES ('Dimension Set Entry',        'GL_08_DimensionSetEntry',       'GL')
INSERT INTO #Tables VALUES ('Dimension',                  'GL_09_Dimension',               'GL')
INSERT INTO #Tables VALUES ('Dimension Value',            'GL_10_DimensionValue',          'GL')

-- Sales / Receivables
INSERT INTO #Tables VALUES ('Sales Invoice Header',       'Sales_01_InvoiceHeader',        'Sales')
INSERT INTO #Tables VALUES ('Sales Invoice Line',         'Sales_02_InvoiceLine',          'Sales')
INSERT INTO #Tables VALUES ('Sales Cr_Memo Header',       'Sales_03_CrMemoHeader',         'Sales')
INSERT INTO #Tables VALUES ('Sales Cr_Memo Line',         'Sales_04_CrMemoLine',           'Sales')
INSERT INTO #Tables VALUES ('Sales Shipment Header',      'Sales_05_ShipmentHeader',       'Sales')
INSERT INTO #Tables VALUES ('Sales Shipment Line',        'Sales_06_ShipmentLine',         'Sales')
INSERT INTO #Tables VALUES ('Return Receipt Header',      'Sales_07_ReturnReceiptHeader',  'Sales')
INSERT INTO #Tables VALUES ('Return Receipt Line',        'Sales_08_ReturnReceiptLine',    'Sales')
INSERT INTO #Tables VALUES ('Cust_ Ledger Entry',         'Sales_09_CustLedgerEntry',      'Sales')
INSERT INTO #Tables VALUES ('Detailed Cust_ Ledg_ Entry', 'Sales_10_DetailedCustLedg',     'Sales')
INSERT INTO #Tables VALUES ('Customer',                   'Sales_11_Customer',             'Sales')

-- Purchases / Payables
INSERT INTO #Tables VALUES ('Purch_ Inv_ Header',         'Purch_01_InvoiceHeader',        'Purch')
INSERT INTO #Tables VALUES ('Purch_ Inv_ Line',           'Purch_02_InvoiceLine',          'Purch')
INSERT INTO #Tables VALUES ('Purch_ Cr_ Memo Hdr_',       'Purch_03_CrMemoHeader',         'Purch')
INSERT INTO #Tables VALUES ('Purch_ Cr_ Memo Line',       'Purch_04_CrMemoLine',           'Purch')
INSERT INTO #Tables VALUES ('Purch_ Rcpt_ Header',        'Purch_05_ReceiptHeader',        'Purch')
INSERT INTO #Tables VALUES ('Purch_ Rcpt_ Line',          'Purch_06_ReceiptLine',          'Purch')
INSERT INTO #Tables VALUES ('Return Shipment Header',     'Purch_07_ReturnShipmentHeader', 'Purch')
INSERT INTO #Tables VALUES ('Return Shipment Line',       'Purch_08_ReturnShipmentLine',   'Purch')
INSERT INTO #Tables VALUES ('Vendor Ledger Entry',        'Purch_09_VendorLedgerEntry',    'Purch')
INSERT INTO #Tables VALUES ('Detailed Vendor Ledg_ Entry','Purch_10_DetailedVendorLedg',  'Purch')
INSERT INTO #Tables VALUES ('Vendor',                     'Purch_11_Vendor',               'Purch')

-- Inventory
INSERT INTO #Tables VALUES ('Item Ledger Entry',          'Inv_01_ItemLedgerEntry',        'Inv')
INSERT INTO #Tables VALUES ('Value Entry',                'Inv_02_ValueEntry',             'Inv')
INSERT INTO #Tables VALUES ('Item Register',              'Inv_03_ItemRegister',           'Inv')
INSERT INTO #Tables VALUES ('Item',                       'Inv_04_Item',                   'Inv')
INSERT INTO #Tables VALUES ('Item Category',              'Inv_05_ItemCategory',           'Inv')
INSERT INTO #Tables VALUES ('Unit of Measure',            'Inv_06_UnitOfMeasure',          'Inv')
INSERT INTO #Tables VALUES ('Location',                   'Inv_07_Location',               'Inv')

-- Warehouse (skip if WMS not used — BCP will simply produce an empty file)
INSERT INTO #Tables VALUES ('Warehouse Entry',            'WH_01_WarehouseEntry',          'WH')
INSERT INTO #Tables VALUES ('Warehouse Register',         'WH_02_WarehouseRegister',       'WH')

-- Master data
INSERT INTO #Tables VALUES ('Currency',                   'Master_Currency',               'Master')
INSERT INTO #Tables VALUES ('Payment Terms',              'Master_PaymentTerms',           'Master')
INSERT INTO #Tables VALUES ('Payment Method',             'Master_PaymentMethod',          'Master')
INSERT INTO #Tables VALUES ('Salesperson_Purchaser',      'Master_SalespersonPurchaser',   'Master')
INSERT INTO #Tables VALUES ('Country_Region',             'Master_CountryRegion',          'Master')

-- =============================================================================
-- Export loop
-- =============================================================================
PRINT ''
PRINT '============================================================'
PRINT 'STARTING EXPORT...'
PRINT '============================================================'

DECLARE @ID        INT = 1
DECLARE @MaxID     INT
DECLARE @SqlSuffix NVARCHAR(500)
DECLARE @FileName  NVARCHAR(200)
DECLARE @SubFolder NVARCHAR(50)
DECLARE @FullTable NVARCHAR(600)
DECLARE @OutFile   NVARCHAR(700)
DECLARE @BCP       NVARCHAR(2000)

SELECT @MaxID = MAX(ID) FROM #Tables

WHILE @ID <= @MaxID
BEGIN
    SELECT
        @SqlSuffix = SqlSuffix,
        @FileName  = FileName,
        @SubFolder = SubFolder
    FROM #Tables
    WHERE ID = @ID

    -- Build full SQL table name: Company$TableSuffix
    SET @FullTable = @Company + '$' + @SqlSuffix
    SET @OutFile   = @Out + '\' + @SubFolder + '\' + @FileName + '.csv'

    -- BCP command: brackets around table name handle spaces and $ correctly
    SET @BCP =
        'bcp "[' + @DB + '].[dbo].[' + @FullTable + ']"' +
        ' out "' + @OutFile + '"' +
        ' -S "' + @Srv + '"' +
        ' -U "' + @Usr + '"' +
        ' -P "' + @Pwd + '"' +
        ' -c -t"," -r"\n"'

    PRINT 'Exporting [' + @FullTable + '] -> ' + @FileName + '.csv'
    EXEC xp_cmdshell @BCP, NO_OUTPUT
    PRINT '  OK'

    SET @ID = @ID + 1
END

-- =============================================================================
-- Disable xp_cmdshell (security best practice)
-- =============================================================================
EXEC sp_configure 'xp_cmdshell',           0; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;

DROP TABLE #Tables

PRINT ''
PRINT '============================================================'
PRINT 'EXPORT COMPLETE. Files saved to: ' + @Out
PRINT '============================================================'

What You Get

After the export script finishes, your output folder looks like this:

C:\NAV_Archive\
├── GL\
│   ├── GL_01_GLEntry.csv
│   ├── GL_02_GLRegister.csv
│   └── ...
├── Sales\
│   ├── Sales_01_InvoiceHeader.csv
│   └── ...
├── Purch\
├── Inv\
├── WH\
└── Master\

Each CSV contains the raw data but no column headers. That is a BCP limitation when exporting in table mode: it dumps rows only, without a header row. For an archive that might be opened months or years later by someone unfamiliar with the schema, headerless CSVs are not ideal.

That is where the second script comes in.

Step 2: Adding Column Headers

After the BCP export, the CSVs are complete but missing headers. I could have used copy /b to prepend a header file, but CMD’s echo command has a well-known issue: it appends trailing spaces to the output, which corrupts header alignment. It can also introduce BOM (Byte Order Mark) issues depending on the codepage.

The cleaner approach is to use PowerShell’s Set-Content and Add-Content cmdlets invoked via xp_cmdshell, which produce clean UTF-8 output without trailing spaces or BOM artifacts.

The strategy for each CSV is:

  1. Read the column names from sys.columns in the correct order (column_id)
  2. Rename the existing CSV to a temp file (_data.tmp)
  3. Write the header row as the first line of a new file using Set-Content
  4. Append the original data rows using Get-Content | Add-Content
  5. Delete the temp file

Run this script in SSMS after the export script completes:

-- =============================================================================
-- AddHeaders-NAV2016.sql
-- Prepend column headers to the 46 CSVs already exported.
-- Run in SSMS AFTER Export-NAV2016-Historical-Tables.sql
-- Does NOT re-export data — only reads sys.columns and patches each file.
-- =============================================================================

USE [YOUR_NAV_DB]
GO
SET NOCOUNT ON;

DECLARE @Out     NVARCHAR(500) = 'C:\NAV_Archive'
DECLARE @Company NVARCHAR(200) = 'YourCompanyName'

EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell',           1; RECONFIGURE WITH OVERRIDE;
PRINT 'xp_cmdshell enabled.'
PRINT ''

-- =============================================================================
-- Same 46-table list (must match the export script)
-- =============================================================================
IF OBJECT_ID('tempdb..#Tables') IS NOT NULL DROP TABLE #Tables
CREATE TABLE #Tables (
    ID        INT IDENTITY(1,1),
    SqlSuffix NVARCHAR(500),
    FileName  NVARCHAR(200),
    SubFolder NVARCHAR(50)
)

-- GL / General Ledger
INSERT INTO #Tables VALUES ('G_L Entry',                  'GL_01_GLEntry',                 'GL')
INSERT INTO #Tables VALUES ('G_L Register',               'GL_02_GLRegister',              'GL')
INSERT INTO #Tables VALUES ('G_L Account',                'GL_03_GLAccount',               'GL')
INSERT INTO #Tables VALUES ('G_L Budget Entry',           'GL_04_GLBudgetEntry',           'GL')
INSERT INTO #Tables VALUES ('VAT Entry',                  'GL_05_VATEntry',                'GL')
INSERT INTO #Tables VALUES ('Bank Account Ledger Entry',  'GL_06_BankAccLedgerEntry',      'GL')
INSERT INTO #Tables VALUES ('Bank Account',               'GL_07_BankAccount',             'GL')
INSERT INTO #Tables VALUES ('Dimension Set Entry',        'GL_08_DimensionSetEntry',       'GL')
INSERT INTO #Tables VALUES ('Dimension',                  'GL_09_Dimension',               'GL')
INSERT INTO #Tables VALUES ('Dimension Value',            'GL_10_DimensionValue',          'GL')

-- Sales / Receivables
INSERT INTO #Tables VALUES ('Sales Invoice Header',       'Sales_01_InvoiceHeader',        'Sales')
INSERT INTO #Tables VALUES ('Sales Invoice Line',         'Sales_02_InvoiceLine',          'Sales')
INSERT INTO #Tables VALUES ('Sales Cr_Memo Header',       'Sales_03_CrMemoHeader',         'Sales')
INSERT INTO #Tables VALUES ('Sales Cr_Memo Line',         'Sales_04_CrMemoLine',           'Sales')
INSERT INTO #Tables VALUES ('Sales Shipment Header',      'Sales_05_ShipmentHeader',       'Sales')
INSERT INTO #Tables VALUES ('Sales Shipment Line',        'Sales_06_ShipmentLine',         'Sales')
INSERT INTO #Tables VALUES ('Return Receipt Header',      'Sales_07_ReturnReceiptHeader',  'Sales')
INSERT INTO #Tables VALUES ('Return Receipt Line',        'Sales_08_ReturnReceiptLine',    'Sales')
INSERT INTO #Tables VALUES ('Cust_ Ledger Entry',         'Sales_09_CustLedgerEntry',      'Sales')
INSERT INTO #Tables VALUES ('Detailed Cust_ Ledg_ Entry', 'Sales_10_DetailedCustLedg',     'Sales')
INSERT INTO #Tables VALUES ('Customer',                   'Sales_11_Customer',             'Sales')

-- Purchases / Payables
INSERT INTO #Tables VALUES ('Purch_ Inv_ Header',         'Purch_01_InvoiceHeader',        'Purch')
INSERT INTO #Tables VALUES ('Purch_ Inv_ Line',           'Purch_02_InvoiceLine',          'Purch')
INSERT INTO #Tables VALUES ('Purch_ Cr_ Memo Hdr_',       'Purch_03_CrMemoHeader',         'Purch')
INSERT INTO #Tables VALUES ('Purch_ Cr_ Memo Line',       'Purch_04_CrMemoLine',           'Purch')
INSERT INTO #Tables VALUES ('Purch_ Rcpt_ Header',        'Purch_05_ReceiptHeader',        'Purch')
INSERT INTO #Tables VALUES ('Purch_ Rcpt_ Line',          'Purch_06_ReceiptLine',          'Purch')
INSERT INTO #Tables VALUES ('Return Shipment Header',     'Purch_07_ReturnShipmentHeader', 'Purch')
INSERT INTO #Tables VALUES ('Return Shipment Line',       'Purch_08_ReturnShipmentLine',   'Purch')
INSERT INTO #Tables VALUES ('Vendor Ledger Entry',        'Purch_09_VendorLedgerEntry',    'Purch')
INSERT INTO #Tables VALUES ('Detailed Vendor Ledg_ Entry','Purch_10_DetailedVendorLedg',  'Purch')
INSERT INTO #Tables VALUES ('Vendor',                     'Purch_11_Vendor',               'Purch')

-- Inventory
INSERT INTO #Tables VALUES ('Item Ledger Entry',          'Inv_01_ItemLedgerEntry',        'Inv')
INSERT INTO #Tables VALUES ('Value Entry',                'Inv_02_ValueEntry',             'Inv')
INSERT INTO #Tables VALUES ('Item Register',              'Inv_03_ItemRegister',           'Inv')
INSERT INTO #Tables VALUES ('Item',                       'Inv_04_Item',                   'Inv')
INSERT INTO #Tables VALUES ('Item Category',              'Inv_05_ItemCategory',           'Inv')
INSERT INTO #Tables VALUES ('Unit of Measure',            'Inv_06_UnitOfMeasure',          'Inv')
INSERT INTO #Tables VALUES ('Location',                   'Inv_07_Location',               'Inv')

-- Warehouse
INSERT INTO #Tables VALUES ('Warehouse Entry',            'WH_01_WarehouseEntry',          'WH')
INSERT INTO #Tables VALUES ('Warehouse Register',         'WH_02_WarehouseRegister',       'WH')

-- Master data
INSERT INTO #Tables VALUES ('Currency',                   'Master_Currency',               'Master')
INSERT INTO #Tables VALUES ('Payment Terms',              'Master_PaymentTerms',           'Master')
INSERT INTO #Tables VALUES ('Payment Method',             'Master_PaymentMethod',          'Master')
INSERT INTO #Tables VALUES ('Salesperson_Purchaser',      'Master_SalespersonPurchaser',   'Master')
INSERT INTO #Tables VALUES ('Country_Region',             'Master_CountryRegion',          'Master')

-- =============================================================================
-- LOOP: prepend headers to each CSV
-- =============================================================================
PRINT '============================================================'
PRINT 'ADDING HEADERS TO CSV FILES...'
PRINT '============================================================'

DECLARE @ID       INT = 1
DECLARE @MaxID    INT
DECLARE @SqlSuffix NVARCHAR(500)
DECLARE @FileName NVARCHAR(200)
DECLARE @SubFldr  NVARCHAR(50)
DECLARE @FullTable NVARCHAR(600)
DECLARE @CsvFile  NVARCHAR(700)
DECLARE @TmpFile  NVARCHAR(700)
DECLARE @Headers  NVARCHAR(MAX)
DECLARE @Cmd      NVARCHAR(4000)

SELECT @MaxID = MAX(ID) FROM #Tables

WHILE @ID <= @MaxID
BEGIN
    SELECT
        @SqlSuffix = SqlSuffix,
        @FileName  = FileName,
        @SubFldr   = SubFolder
    FROM #Tables
    WHERE ID = @ID

    SET @FullTable = @Company + '$' + @SqlSuffix
    SET @CsvFile   = @Out + '\' + @SubFldr + '\' + @FileName + '.csv'
    SET @TmpFile   = @Out + '\' + @SubFldr + '\' + @FileName + '_data.tmp'

    -- Step 1: Build header row from sys.columns in column_id order
    SET @Headers = ''

    SELECT @Headers = @Headers +
           CASE WHEN @Headers = '' THEN '' ELSE ',' END +
           c.name
    FROM sys.columns c
    JOIN sys.tables  t ON c.object_id = t.object_id
    WHERE t.name = @FullTable
    ORDER BY c.column_id

    IF @Headers = ''
    BEGIN
        PRINT 'SKIP (no columns found): ' + @FullTable
        SET @ID = @ID + 1
        CONTINUE
    END

    PRINT 'Processing: ' + @FileName + '.csv'

    -- Step 2: Rename data file to .tmp
    SET @Cmd = 'rename "' + @CsvFile + '" "' + @FileName + '_data.tmp"'
    EXEC xp_cmdshell @Cmd, NO_OUTPUT

    -- Step 3: Write header using PowerShell (clean UTF-8, no BOM, no trailing spaces)
    SET @Cmd =
        'powershell -NoProfile -Command "' +
        'Set-Content -Path ''' + @CsvFile + ''' -Value ''' +
        REPLACE(@Headers, '''', '''''') + ''' -Encoding UTF8' + '"'
    EXEC xp_cmdshell @Cmd, NO_OUTPUT

    -- Step 4: Append original data rows after the header
    SET @Cmd =
        'powershell -NoProfile -Command "' +
        'Get-Content -Path ''' + @TmpFile +
        ''' | Add-Content -Path ''' + @CsvFile + ''' -Encoding UTF8' + '"'
    EXEC xp_cmdshell @Cmd, NO_OUTPUT

    -- Step 5: Delete temp file
    SET @Cmd = 'del "' + @TmpFile + '"'
    EXEC xp_cmdshell @Cmd, NO_OUTPUT

    PRINT '  OK'
    SET @ID = @ID + 1
END

-- =============================================================================
-- Disable xp_cmdshell
-- =============================================================================
EXEC sp_configure 'xp_cmdshell',           0; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;

DROP TABLE #Tables

PRINT ''
PRINT '============================================================'
PRINT 'HEADERS ADDED. All CSV files now include column headers.'
PRINT '============================================================'

After both scripts finish, opening any CSV in Excel (or any data tool) will show proper column headers in the first row:

Entry No_,G_L Account No_,Posting Date,Document Type,Document No_,Description,Amount,...
1,1100,2016-01-15,2,INV-001,"Opening Balance",15000.00,...
2,2100,2016-01-15,2,INV-001,"Accounts Receivable",15000.00,...

This makes a significant difference for long-term archiving. A headerless CSV that sits untouched for three years becomes a puzzle when someone finally needs it during an audit. With headers, anyone can open it and immediately understand what each column represents, no NAV knowledge required.

The Final Output

In this particular engagement the full CSV package came out to about 9 GB across all 46 tables, while the .bak was around 88 GB. Both went to SharePoint for long-term storage.

Key Takeaways

Always take the .bak first. The CSV exports are the accessible layer, but the SQL backup is the safety net. If an auditor asks about a table you did not export, you can still restore and query it.

Do not fight PowerShell for this. I spent more time than I’d like to admit debugging System.Data.DataTable null indexing in PowerShell. T-SQL + xp_cmdshell + BCP is a simpler stack for this job — everything runs inside SQL Server with no external dependencies.

Know your table naming convention. The Company$Table_Name pattern with underscores replacing dots and slashes is easy to miss if you have only ever worked from the NAV client side. Always verify against sys.tables before running any bulk export.

BCP has no row limit. Tables like Value Entry can be enormous (I have seen them over 5 GB on busy installations). BCP handles this gracefully; Excel will not.

Always include column headers. BCP in table mode does not export headers. The AddHeaders script solves this cleanly using sys.columns and PowerShell, avoiding the trailing-space and BOM issues that CMD’s echo and copy /b can introduce.

Prefer Windows Authentication. If your environment supports it, use -T (Trusted Connection) instead of -U/-P in the BCP command. This avoids storing SQL credentials in plain text inside the script, which is especially important if the script gets archived alongside the data.

One More Thing: the Dimension Set Entry

This one gets forgotten in almost every NAV decommissioning I have seen: Table 480 — Dimension Set Entry. Without it, all your GL entries and posted documents lose their dimension values (cost center, department, project, etc.), which makes the data significantly less useful for any future analysis or audit. Make sure it is in your list.

If you are in a similar situation — a legacy NAV instance that needs to come down and you need to preserve the data cleanly — I hope this saves you a few hours. The scripts above are generic enough to drop into any NAV 2016 (or earlier NAV/Navision) installation. Just swap the company name, server, and database, and you are good to go.

Questions or comments? Drop them below or reach out directly.

— Ivan

Post Views: 438

Leave a Reply

Your email address will not be published. Required fields are marked *