Restore Backup time - in Nav 4.0 SQL

janmib

The version is 4.0 sp3 - running on new hp server with win sql 2005
A total of 89 companies in the database - No performance trouble
in day to day ops.

I did a backup of a company from my development net - the company
has almost no data -

Then started restore to my production server over night.
Total restore time 16 hours !!!
No users while restoring the company.

It seem like restoring time increases exponentially pr. added
company in the database -

Any advice will be greatly appreciated!

DenSter

What do you expect? With almost 100 companies, you're looking at 150000 tables in one database. A NAV restore is just slow, because of the way NAV communicates with SQL Server.

Look at your transaction log, see if there's enough room to grow. It might be auto expanding while you're restoring. Same goes for the data files.

Did you start the restore on the SQL Server or from a networked computer? If you're on a networked computer try restoring directly on the SQL Server.

cnicola

I had a client with 10-20 companies and once they started using it a little the full Navision restore would just never work. I ended up doing few companies at a time.
But as a on-going procedure I would recommend restoring straight from SQL backups when you need a dev environment.
Hope that helps.

janmib

DenSter wrote:

What do you expect? With almost 100 companies, you're looking at 150000 tables in one database. A NAV restore is just slow, because of the way NAV communicates with SQL Server.

Look at your transaction log, see if there's enough room to grow. It might be auto expanding while you're restoring. Same goes for the data files.

Did you start the restore on the SQL Server or from a networked computer? If you're on a networked computer try restoring directly on the SQL Server.

I started the restore from a networked computer - will try on the server.
Plenty of room to grow for the t. log

I dont really see your point - I'm adding 1 company - thats not 150000
tables - aprox 800 tables and I cant see why this should take som much time.....

DenSter

Oh you're doing just one company, I thought you were doing a complete restore. It depends really what is in that company. 16 hours does sound a bit too much, and it is on the hight side, but it is certainly not unheard of for NAV restores to take that long per company.

cnicola

I thought you meant multiple companies as well.
OK I guess to be thorough:

1. What type of machine are you restoring on?
2. Do you have the log and the db files on same drives?
3. And restoring over the network can be slow depending on a lot of factors.
4. And also important: what do you mean by "almost no data"? It can be a very relative term

janmib

cnicola wrote:

I thought you meant multiple companies as well.
OK I guess to be thorough:

1. What type of machine are you restoring on?
2. Do you have the log and the db files on same drives?
3. And restoring over the network can be slow depending on a lot of factors.
4. And also important: what do you mean by "almost no data"? It can be a very relative term

1 - Server - HP dl380 - 4gb ram - dual cpu - 3 mirrored volumes
(1 volume system - 1 for sql data - 1 sql log)

Client PC HP dc7600 1 gb ram

2 - log and database file is not on same drive

3 - Network 1gb

4 -I added data in table 79 - the rest i standard data in aprox 15 tables.

DenSter

All I can think of is that it is trying to restore data into a common table and it is locked out by SQL Server. I've seen a reindex take an hour for just one table, and it was a table that only has one record, but it was being updated constantly. One GB should not take 16 hours to restore.

kriki

[Topic moved from Navision forum to SQL Performance forum]