Skip to main content

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

Comments

  1. Hi Saurav,

    how to Convrt .FDB file into SQL.could you please provide any steps you have.
    Thanks in Advance.

    ReplyDelete
  2. how to restore .FDB file into SQL Database.

    ReplyDelete
    Replies
    1. Hi,
      After following listed above, you can take a native backup of database.
      Then create a database using finsql and restore the backup taken.
      The database will get converted to SQL.

      Delete
  3. Hi,
    After following listed above, you can take a native backup of database.
    Then create a database using finsql and restore the backup taken.
    The database will get converted to SQL.

    ReplyDelete
  4. Thank you for the useful information.

    ReplyDelete

Post a Comment

Popular posts from this blog

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

Microsoft Dynamics NAV 2016 - How to Configure Phone Client.

Hi All, In this article we will discuss how we can connect Microsoft Dynamics NAV 2016 with New Client Launched i.e. Phone Client. This Article Contain Steps for a Android Phone as I have Only Android Phone. I am doing it having all tiers on my windows 8 machine, steps remain same for multiple servers but issues might be different. What we Need (Other what we discuss in this article) -  The Service Tier should be on Public IP . Some of the Data-card does not Provide you Public IP. check it for sure.

Download Microsoft Dynamics Business Central on Premises 2020 Wave 2 Release.

 Hi Readers, As Expected Microsoft have released #msdyn365bc 2020 wave 2 is available.  This article provides details of the new release. The 2020 release wave 2 for Business Central delivers a wealth of new features. Read What's new in 2020 Release Wave 2 here .  Dynamics NAV Customers upgrading to Business Central on Prem will be able to run business central on premises, move to cloud or choose a hybrid approach.