How does Navision's restore work internally on SQL?

RobertMo

We all know how Navision’s restore works from its dialog window. That means restoring TableData of each selected company, common data and objects (last 2 if selected and if DB is new) and at the end secondary keys (and sum-index tables).

But I want to know more in detail? Especially I am interested in estimating the timing?

What I have noticed is that the speed (reported as KB copied) is changing per tabledata. I noticed that the table 355 is very slow in KB, while table 17 is much faster.

I checked the list of table sizes and number of records and came to following assumptions:
Navision restore takes a batch of records (eg. 1000 or 2000 or 10000?) and inserts them into DB. And the average time to insert this batch is more or less constant (but of course HW dependant).
Therefore inserting 10000 records of table 355 takes similar time span as 10000 records of table 17, but since the size of the record is very different, thus also more KB are reported during restore of T17 then during T355.

After setting asumption I have checked other tables and it somehow confirms the idea.
Of course the speed is not linear to the records size reported in "Table information", becuase the record size is highly dependant also on the secondary keys, which means you can not simply do the math based on what the “Table information” reports.

But this is only my assumption? Can someone confirm or explain how Navision’s restore actually insert data to DB?

ara3n

the whole restore and backup is closed and you can't tell from front end.

All I have found out is that it first insert the data into 20 Million range first. So it creates new tables on sql. Once all the data is transfered from fbk to into these 20 mil tables, then it transfers them into actual tables. Since it's a new company, it creates the tables again for the actual company and then transfers them. Afterwards it deletes the 20 mil records and deletes the table objects in 20 mil

kine

You can use the SQL Profiler to see, what it is doing in background... :-)

ara3n

right from backend you can run profiler. I just haven't found the time to look at a backup routine. I think I can find more interesting things to do than analyze that. Unless of course a customer wants to pay for it, which I doubt.

RobertMo

Yes I was thinking about SQL profiler, just didn't (and still don't) have the time to do it.
I thought someone else already got curious once and did it and could share some ideas.

Well anybody?

kine

No, no time for that... :-) and it seems like others have no time too... 8)

kriki

[Topic moved from Navision forum to SQL General forum]