Backup Query

vikram7_dabas · 2010-03-30

Dear concern
I m using NAV 4.0 SP3 SQL DB.I m taking Backup,i want while taking backup through Navision, data of 1 table (say,Table ID 405) should not come in Backup.Can we do this.Is it possible guys?

stryk · 2010-03-30

Not out-of-the-box.
Actually you need to move the separated tables into a new Filegroup; then you could create FG Backups, excluding the tables/FG you don't want to save.

But caution: depending on the tables you skip, you might jeopardize the data-integrity in case of a restore of such an "incomplete" backup!

vikram7_dabas · 2010-03-30

stryk wrote:

Not out-of-the-box.
Actually you need to move the separated tables into a new Filegroup; then you could create FG Backups, excluding the tables/FG you don't want to save.

But caution: depending on the tables you skip, you might jeopardize the data-integrity in case of a restore of such an "incomplete" backup!

How can I move the separated tables into a new Filegroup, and what do u mena by FG Backups.How can I take these backups?

stryk · 2010-03-30

Again, technically this is possible, but practically not recommended. In cases of T405 "Change Log Entry" this is probably pretty fail-safe, but with other tables you might raise a problem.

The general proceeding could be like this (all to be done in Management Studio using TSQL):
- Create new FG and DB file in new FG
- DROP all Non-Clustered Indexes (NCI)
- DROP Clustered Index (CI) using the MOVE TO clause to move the data into the new FG
- re-CREATE all in NCI in new FG
Then you could create FG Backups, only saving FGG "PRIMARY" and "Data Filegroup 1"

See SQL Server "Books Online" about details.

vikram7_dabas · 2010-03-30

Thanxs.Is it possible through NAV,because my Database is in Native.

stryk · 2010-03-30

vikram7_dabas wrote:

I m using NAV 4.0 SP3 SQL DB.

Uhm ... so what? You said you're running SQL ...
No, not a chance with C/SIDE Server.

einsTeIn.NET · 2010-03-30

You could copy your database, delete all data in table 405 in the copied database and make a backup. But that's only a one time solution, not for daily backups. And as mentioned this doesn't work or is very risky for other tables.

kapamarou · 2010-03-30

The main question is why?

If this is not a one-time task then instead of skipping the table during backup, you could simply empty it. If you have permissions to do that, and you don't want to keep the information then why keep it? Empty it and set it up correctly so that it doesn't store too much info (if your problem is the number of entries).

stryk · 2010-03-30

kapamarou wrote:

The main question is why?

Indeed! Just a good guess:
They are logging way too much, this "Change Log Entry" is a sort of "table monster", wasting precious disk-space, also with the backups.
Just this week I've worked on a database of 17GB net size; and alone T405 used 8GB!

So I agree, the preferred steps should be:
- review the Change Log setup; only logging what's really necessary
- delete old data periodically

rdebath · 2010-03-30

I hesitate to suggest this, but change log is one of those tables that it's possible to put into 'DataPerCompany=No' . If it's not in a company they it won't be saved in a company backup ...

Just saying.

David_Singleton · 2010-03-30

vikram7_dabas wrote:

Dear concern
I m using NAV 4.0 SP3 SQL DB.I m taking Backup,i want while taking backup through Navision, data of 1 table (say,Table ID 405) should not come in Backup.Can we do this.Is it possible guys?

Take a look at report 510 it does what you need.

Backup Query

Comments

Categories