restore SQL db to native error

Business Central/NAV Technical Forum
1

hi all,

i have a database with 10 companies (A1~A10)

A10 is the HQ (with the highes t number of transactions, data)

The rest of the companies A1~A9 i have no problem restore from fbk file to my native.
My local db size can only set to 62GB.

I check my LIVE database the size is 120GB (using similar licence)

Why is the LIVE (SQL) able to accomodate such a big file size while my local unable to ?
Is there anyway that I can restore to my local ? (Eg: Any compressions of data)

Error:
there is not sufficient space available in the database to perform this task. You can obtain more space by:
*Expanding the database.
*Deleting the unnecessary data.
*Date compressing older, non-current entries.
*optimizing the database.

2

By the way, when i restore ONLY company A10 from LIVE, the resulting fbk files is a little bit ‘funny’ instead of 1 fbk files,
it produce about 15pcs of files(*.fbk) eack aprox 2GB
[H]

3

yes. that’s how the backup works in native db. when you give a name, eg. backup_1.fbk , and the backup is more that 2 Gb, there will be backup_11.fbk, backup_12.fbk, backup_13.fbk, and so on.
when you restore, you only need to choose the first .fbk file.

4

joe,

thanks for replying… when i restore it keep getting error no matter how much dtabase size is expand. i check the total restored *fbk file size = 28GB, my local database
have additional of 30GB which would be sufficient.

But why do I keep getting that error ?

[:’(]

5

Why is your “local limit” 62GB (I guess it’s 64GB)? What SQL Edition do you use? I suggest using the SQL Server Developer Edition which has the same features as Enterprise and no limitations on the db size …

6

hi Stryk,

I had expand it to 64gb already…
i do not have SQL Server Developer Edition, but 1 thing i am not sure why would i need sql since this is a local database (c/side client).

7

Yes, Jorg mentioned that you need a version of SQL server that can accommodate db size as big as what was your live db size, 120Gb.

and yes right, you can restore it in your native (c/side) db also, instead building a sql server in your computer.

8

Database Used(KB)-17%

before restore company A10 size-10GB

when restore company A10 error occurred size-64GB

But FBK_1~ to FBK_14 total = 30GB only!

9

joe,

local database specs as follows:

Database Used(KB)-17%

before restore company A10 size-10GB

when restore company A10 error occurred size-64GB

But FBK_1~ to FBK_14 total = 30GB only!

10

i suspect there is a problem when you did a backup.

do you have another set of backup? you may need to try to do another backup, do not replace what you have now.

11

did both already… just like to check… if my license is 64gb and total fbk back up (2gb*14files=28gb) it should be enough…

12

I’m not sure is it true for Native also, but when restoring to SQL, you must have approx twice the size of resulting DB to restore the backup (or allow autogrowth for the DB, as you can’t tell before how much space precisely will be needed.)

Restore is performed as one HUUUGE transaction, that for twice the space is needed. On successful restore the translog can be cleaned up.

This refers to SQL DB, but IMHO the same happens in Native, too - however, it’s my guess an might not be 100% truth…

13

Joe,

Let me clarify, you mention I can restore to native as long as i have a version of SQL MGT STDIO v10.

so far not sure if I can meet the requirements with specs below:

local physical memory [130GB]
SQL MGT STDIO v10.0

=======================================================

LIVE

Database PRIMARY File: 600MB
Database Secondary File: 122GB
TRANSACTION LOG: 93GB
TOTAL: 215.6GB

======================================================

Company A10 requires about 122 GB

14

please note a post from Modris for further consideration.

if you plan to restore into native db, you don’t need sql, at all.
if your backup (.fbk) size is 28gb, you will need to create new database in native with size 56gb (twice of your .fbk), minimal.
when restoring, it can be that the db size is not enough, so you can just expand it 20% or 30%, and re-run the restore. it will continue from last state (not re-do from beginning).

it can happen that it’s still not enough, and NAV gives you warning (again), just repeat by expanding it 20% or 30%, and re-run the restore.

15

Joe,

there’s no way to create in native given the license limitation… *sigh…

16

One additional quick idea… Do you have Analysis Views defined? These may take up very much space in the DB, but you can easily delete them with no harm for system.

By “delete” I mean not deleting them completely, but cleaning up. In AV definition change something, I usually change Starting Date - this causes Navision to delete previously created AV entries, freeing up used DB space. Make a backup before Updating your AVs again with the changed definition - it can appear to be considerably smaller in size.

There are other things that can be done to reduce DB size - as deleting invoiced and/or archived docs from “ancient ages”, and date compression for closed FYs.
However, such methods give significant effect in case the DB is used for many years - if you managed to accumulate your ~120GB of data in a couple years of heavy-load operating, the size freed up might be smaller.

The trick with cleaning up AVs I do regularly - in one of my DBs they take roughly 1/4 of all the space (which is not normal, but client is always right).

It speeds up backup-restore processes, and anyway, for common everyday usage AVs do not need to to contain detailed entries for more than 1-2 FYs backward, older data can be summed in one opening entry. This, in turn, speeds up the AV performance itself, it has less records to process.