How To - Restore Native Backup to SQL Database. (SQL Migration)

Hi All,

In this post we will be discussing how we can move a Native Database to SQL Database.
In Simple Words If a customer is Using Native Database (FDB), how we can move that database to SQL Server.

So if you know how to do SQL Migration rest is something you will not be interested in. Others who want to learn it please read the complete article.


What is the Requirement?

1. I have a customer using Native Database (FDB).

** Fact - Last Native Database was Supported with NAV 2009 R2.

2. I Tried to take Navision backup of the customer database and restore it on a New Database created on SQL using finsql.

3. During restoration I ended up with an error message as below -

The <<Table Name>> table contains a field with a date value that cannot be used with SQL Server:

Record: Document Type='Order',No.='2001'
Field: Order Date='01/06/0112'

What is the Issue?

Native Database Does Not Validate the Dates when entered by Users. But SQL have some validation on date fields as it have a start and end date that it can accept. If value falls within the allowed range of DateTime SQL Field it let the data restore otherwise it throws an error message as above.

How to Resolve?

Microsoft have provided an object for the correction of this. How to use is described Below.

Steps to resolve the issue -

1. From the Product DVD of the Client Version Navigate to Folder - UpgradeToolKit\SQL Migration.

In My Case the Product DVD is 6.0 SP1 IN.

2. Import Migrate.fob in the database and below listed objects will be imported in Native Database.

3. Run Codeunit 104010, you might end up with below error. If Not GO TO STEP 5.

4. Design Codeunit 104010 and change the Location to Non Root Drive as shown Below.

Save the Codeunit.

5. Run the Codeunit 104010 again, After Successful Execution it will prompt a confirmation message.

** Execution Time Depends on the Data in Database. Please be patient.

6. As per the Message let's import the Codeunit from the path and compile it.
** File Name - FIELDCHK.txt

7. Run the Codeunit 104015. It will check for all data in the database which is not acceptable by SQL Server Database.

** Execution Time Depends on the Data in Database. Please be patient.

8. Once the Job is complete System will popup a total count of Incorrect Values that are not acceptable by SQL Server.

9. Once Clicked OK, a Form get opened where incorrect and Suggested new Values will be Filled in for every incorrect record.
 -- Copy list in Excel and send it to customer.
 -- Finalize the correct value with Customer.
 -- Update Same on the New Value Field as suggested by Customer.

10. Once Update Done Click ESC or Close the Form. It will ask for Confirmation.

 -- Make sure you say YES on Both Otherwise you need to repeat activities after Step 5 again.

11. Once System applies the entries, take a Navision backup again and restore it on SQL Database.

It will get restored without any issues.

Hope you find the post useful.

Thanks & Regards,
Saurav Dhyani

www.sauravdhyani.com