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.
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.
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.