Restoring backup to Navision 4.0 sp1 running on Sql 2005

My database consists of approx 50 companies - (less than 1GB)

Servers etc. are completely new - lots of memory etc. etc.

The time to restore one single company is now about 50 min.

The backup I restore is a template company with basic data.

Doing this in a Native db takes only a few minuttes.

It seems that writing and deleting records in the objects table is taking a lot of time - I can’t se

why.

Well, I hope you have browsed this forum for all advice for “SQL Server Performance” !?

First, when you restore a FBK into SQL Server db, all tables have to be created there, which takes some time, then data is imported and indexes etc. created …

Basically a restore is a “hell” of a write-transaction(s), thus probably your disk-configuration is a major bottleneck … how’s your setup?

My setup is :

Fortunately hardware is not an issue …

Well, the behavior you see is “normal” behavior of NAV: SQL Server handles the “Company Management” different than native; most tables are multiplied per Company which you could see from the SQL Server site table name.

For Example; the NAV table “Item” is generated like “Company 1$Item”, “Company 2$Item”, etc…

And of course this takes its time … I’m afraid there’s hardly a chance to speed things up …

I still can’t see any reason why the backup - for each new company - takes longer and longer time to perform.

And writing/crating about 870 tables don’t take that amount of time. The process of renaming a company takes about 5 min- this timespan does not change.

For some reason all objects eg. tables - pr. company is rewritten in the objects table for each restore I perform. At least this is what I suspect, and don’t understand.

Thanks anyway - still hoping someone may have a bright idea.

And in the same note - any better way to completely clone a company ??

When you restore on sql. The way navision restores the fbk is that it first reads the fbk and creates new tables in million range and writes the data in those tables. Once it coppied the data, it creates the actual company and transfers the data from the million range tables into the actual tables. After this process it deletes the million range tables. This is specially evident when you try to restore a backup that is from another database. When you get an error during restore, you can close the client, open another client and click on restore and navision will ask you if you want to continue finishing the backup.
The eaisiest way to completly clone a company is to create the company in navision. Then create a DTS package in SQL to copy all the data from the tables.

Don’t forget that MS SQL 2005 is fully supported in v 4.00SP2 + update 1… older versions have problems with MS SQL 2005…