mibuso.com

Microsoft Business Solutions online community
It is currently Thu May 23, 2013 1:15 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Navision Backup through SQL company wise
PostPosted: Thu Feb 25, 2010 9:41 pm 
Offline

Joined: Mon Jan 25, 2010 8:46 am
Posts: 20
Country: India (in)
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


Top
 Profile E-mail  
 
 Post subject: Re: Navision Backup through SQL company wise
PostPosted: Thu Feb 25, 2010 10:29 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 639
Location: Nürnberg
Country: Germany (de)
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?

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: Navision Backup through SQL company wise
PostPosted: Thu Feb 25, 2010 11:51 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5315
Location: Prague
Country: Czech Republic (cz)
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.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Navision Backup through SQL company wise
PostPosted: Fri Feb 26, 2010 4:57 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8700
Location: 3rd rock from sun
Country: United States (us)
http://mibuso.com/blogs/ara3n/2009/12/0 ... amics-nav/

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Navision Backup through SQL company wise
PostPosted: Fri Feb 26, 2010 9:15 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 639
Location: Nürnberg
Country: Germany (de)
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 ...

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: Navision Backup through SQL company wise
PostPosted: Fri Feb 26, 2010 10:48 am 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 547
Location: Aalborg,Denmark
Country: Denmark (dk)
stryk wrote:
as you CANNOT restore such a backup into the real live database.

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

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: Navision Backup through SQL company wise
PostPosted: Fri Feb 26, 2010 11:06 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 639
Location: Nürnberg
Country: Germany (de)
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 ...

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: