Rashed Amini

The ara3n weblog

Backup/Transfering Company Specific Data through SQL for Dynamics NAV

8th December 2009

Dynamics NAV client has a feature to backup and restore company specific. This process creates an (.fbk) file that contains just the data without indexes. The (.fbk) file can be used to restore in another database. For large database this is a slow process, and not feasible to do. In addition Dynamics Developer license has recently changed in that partners no longer can delete standard NAV fields or fields added by Add-ons by an ISV. So if a client wants to remove an Add-on from its database, they won’t be able to and will be stuck with the fields in the table, unless they call the ISV to delete them for them with their license.
The following solution is a method to remove these fields, but this can also be used to Backup Company specific Data through SQL. This lowers the time required to do Company specific backups, but it also allows a faster way to transfer setup data from one company to another company. For example, customers want to periodically refresh their test database with production data.
BackupNAV

The solution consists of a processing report that generates a sql script. It only works with 5.0 sp1 and higher. This is because it doesn’t includ the SIFT tables. I’m sure with some modification you could run it for older versions. As shown in the picture above the user selects a file name, destination database name, and company name. Once the user clicks OK, the report generates a sql script that copies the data from source table to a destination table. Below is a screenshot of the SQL script.

SQLScript

The steps for the whole process of copying a specific company through sql
1. Export all object from Source Database.
2. Create a new database and import the objects from step 1.
3. Create a blank company.
4. In Source Company, run this new report. Specify the destination Database and Company.
5. Open the sql script and execute it. (It should run without any errors)
6. Use the new company.

I mentioned above that you can also use this method to remove unwanted fields from a given table. The change that you would need to do in step 2 is not to load object with the unwanted fields and instead load new object. Then in step 5 find the fields in scrip file and remove them from the INSERT and SELECT statement. This way only the fields that you want to copy are transferred to the destination database.

I’ve included the ProcessingReportObjects.fob.

7 Responses to “Backup/Transfering Company Specific Data through SQL for Dynamics NAV”

  1. erho Says:

    Thanks Nice tool! You should add the following line to the function insertFields():

    Field.SETRANGE(Enabled, TRUE);

    Otherwise you get an error when fields are disabled in the table.

  2. dorenthas Says:

    Awesome! That’s definitely something we could use here at our company. You nailed it right on the head when you wrote “customers want to periodically refresh their test database with production data” :)

    There’s another advantage to this that you could mention. When you backup a NAV database using the NAV client (.fbk), tables are locked for the duration of the backup process (so it’s a bad idea to do that during business hours), whereas that wouldn’t be an issue with your solution using SQL scripts.

  3. ara3n Says:

    To prevent locking, the script needs to change and WITH LOCK needs to be added to the command.

  4. Kriki’s Dynamics NAV blog » Clustered or not clustered Says:

    […] scratch and also create the clustered index on them. After that you can use ara3n’s report (http://mibuso.com/blogs/ara3n/2009/12/08/baking-uptransfering-company-specific-data-through-sql-for-…) to create a script to copy all data from 1 company to the new company (BTW: I am working on my own […]

  5. Kriki’s Dynamics NAV blog » Downgrading after accidental upgrade Says:

    […] I needed to do it some time ago (luckily NOT for production but just for some testing) and used ara3n’s tool (http://mibuso.com/blogs/ara3n/2009/12/08/baking-uptransfering-company-specific-data-through-sql-for-…) […]

  6. Pam Says:

    I downloaded the fob but found I cannot reference the report number based on our license. Any suggestion on how else to get at this demo object?
    Thanks!

  7. ara3n Says:

    you can download kriki version in text from here

    http://www.mibuso.com/dlinfo.asp?FileID=1171

Leave a Reply

You must be logged in to post a comment.