Navision Backup through SQL company wise

sameer_79

Dear All,
i have navision 4 DB with 2 companies,i want to take only one company but through SQL not Nav client,it is possible???

Regards

stryk

Well, to accomplish this you need to have 3 or 4 Filegroups in SQL Server:

1) PRIMARY (Standard); contains system objects
2) Filegroup Company A; to store all tables from company A
3) Filegroup Company B; to store all tables from company B
4) Filegroup Common; to store all tables which are common for all companies

Then you could move all tables into the specific filegreoups:
- DROP all Non-Clustered-Indexes (NCI)
- DROP Clustered-Index using MOVE TO clause to transfer the data into the new filegroup
- CREATE Clustered-Index in new filegroup
- CREATE all Non-Clustered-Index in new filegroup

(of course there's NO chance to do this with NAV - you need to createTSQL scripts)

Then you could use the BACKUP DATABASE command using the FILEGROUP specification; hence you could backup just a single company. But to grant data integrity, you always have to save multiple FG; for example:

PRIMARY + COMPANY_A + COMMON
or
PRIMARY + COMPANY_B + COMMON

Then be aware, that whenever you change/save a table in "Object Designer" indexes could be reset to the default FG.

So, YES - technically it is possible to save just one company, but this requires a LOT of effort and SQL programming.
I'd say it's easier to use the native NAV backup ... :-k

Last but not least: why do you want to do this?

David_Singleton

Didn't Rashed (ara3n) create a program to do this and then shared it on Mibuso? I am pretty sure I saw something, but not sure if in downloads or blogs or tips and tricks.

ara3n

http://mibuso.com/blogs/ara3n/2009/12/0 ... amics-nav/

stryk

Ah! Haven't seen this before! Nice! =D>
So you just copy the data into a new database. But this could also mean a huge workload on large databases. Also, you have to repeat this process completely from the scratch with ever Company Backup you want to create.

IMHO for a "one shot" solution this is great (e.g. for setting up a Test-DB or something), for a permanent solution I guess this is not really feasible ... especially, as you CANNOT restore such a backup into the real live database.

So again, it depends on the purpose ...

pdj

stryk wrote:

as you CANNOT restore such a backup into the real live database.

Why not? You could just copy it back...

stryk

pdj wrote:

stryk wrote:

as you CANNOT restore such a backup into the real live database.

Why not? You could just copy it back...

True. But I do not consider this as a normal SQL Backup/Restore proceeding.
If you need to restore, you need to 1) restore the "One_Company_DB" from the SQL BAK, then transfer the data back into the real DB.

Again, for a small database this might be OK, but for a large system I'd say this is way too much effort ...