Hi

I'm new of this forum and I don't speak very well english.

Now I've a problem for backup BAAN.

My situation:
Server Win2000 SP3 (2x550MHz, 2Gb, 2x18GB Hd Raid1 + 4x36GB Raid1)
Baan Server IV C SP6
Now I backup with ArcServe 2000 with all patch.

I've read that I can't backup and restore a single table: can you confirm that?

What I can do for do that?
1)I' ve to buy a ArcServe upgrade (other than SQL option...)
2)Buy another backup program like Veritas Backup (I see in the datasheet that this software can do this, I hope it... :D )
3)I do a full dump with Baan? (Note I've a 30Gb SQL database and a single company of 4Gb take 8 HOURS!!! This is good?! Can I speed up this procedure?)

Thanks

This 4GB is the company size of the dump or inside SQL?

On several UNIX machines I can dump 2GB (inside Oracle)/hour.
I also improved the export by using a better db_resource (by using a higher nr. of rows fetched).
Finally, I get a huuge improvement by not dumping empty tables (first I collect a list of tables with count(*) > 0) and then use this list to build one that I use with bdbpre6.1 ; this also allows me to dump companies that share tables without interference one with the other and improve my imports.

(on HP-UX I even compress each table dump in the background as soon it's dumped to save diskspace, good when you are short of diskspace).
____________________

For your current setup, if that software is doing an online/offline backup of the database tablespaces, and if SQLserver is similar to Oracle, that means that your restore is limited to per tablespace, not per table. You can do restores of tables, but using a replicated database (PIT with export of the table from this DB copy).

Hi,

You definately can backup single table of Baan using Create Sequential Dump of table and specify the field seperator. It creates the output with field seperator which can be imported back.

Pl let me know if you need more details.

-- Avinash

why do you want to be able to backup and restore single table?

my suggestion is try the DTS function of SQL. It should be a lot faster than Baan's Create Sequential Dump.

What is DTS function of SQL ?

Thanks

Max

If you open your Enterprise Manager. Go to Microsoft SQL Servers. Then open your server name. It the folder below Databases called Data Transformation Services.

I'm not a big backup / system person, but I think I can explain 'why do you want to backup and restore a single table'. The problem is not so much backing up the tables as recovering a single table. Some backup software supports this, others don't. For my case, we were using Backup Exec, which has early versions that did not support single table recovery, and later versions that did. Systems people tell me it also depends on how you do the backup, what recovery options are available.

For Arcserve (on NT/Win2k anyway), you need to purchase a separate agent for SQL to be able to backup and restore individual tables without dumping them first.

First I must thanks you for help.

[Victor_Cleto]
>export faster ideas
>This 4GB is the company size of the dump or inside SQL?

4.5Gb of text file dumped.

>On several UNIX machines I can dump 2GB (inside Oracle)/hour.
>I also improved the export by using a better db_resource (by using a higher nr. of rows fetched).
>Finally, I get a huuge improvement by not dumping empty tables (first I collect a list of tables with count(*) > 0) and then use this list to build one that I use with bdbpre6.1 ; this also allows me to dump companies that share tables without interference one with the other and improve my imports.
>(on HP-UX I even compress each table dump in the background as soon it's dumped to save diskspace, good when you are short of diskspace).

Now I've 36Gb Hd as "temp dump", but I dump entire company with BAAN internal function (I need to save ALL table, also the empty ones).

QUESTION: When I reload a dumped table, my sql database growth 3 times the amount of data that I effectively reload: it's right or there's a problem? Perhaps a log function? (example: dump a 1Gb table with database of 10Gb. After reload the same table with the same data, I see about 13Gb).

[lbencic]
>... Some backup software supports this, others don't. For my case, we were using Backup Exec, which has early versions that did not support single table recovery, and later versions that did. >Systems people tell me it also depends on how you do the backup, what recovery options are available.
[EdHubbard]
>For Arcserve (on NT/Win2k anyway), you need to purchase a separate agent for SQL to be able to backup and restore individual tables without dumping them first.

I use ArcServe 2000 with SQL 2000 connector (we've also the connector for Exchange), but when I access the sql server with baan, I see only all the database and not the tables. How I can backup and restore the table with arcserve if I read explicit on the arcserve support page that I can do this only with SQL 6.5 and not with SQL 7/2K?


Thanks Everyone for the help another time!

> 4.5Gb of text file dumped.

That's big, defenetely.

>Now I've 36Gb Hd as "temp dump", but I dump entire company with BAAN internal function (I need to save ALL table, also the empty ones).

Why exporting empty tables? If later on you need to import the full company data, you delete the existing tables, import and then run create tables before the reorgs. - way faster!

>QUESTION: When I reload a dumped table, my sql database growth 3 times the amount of data that I effectively reload: it's right or there's a problem? Perhaps a log function? (example: dump a 1Gb table with database of 10Gb. After reload the same table with the same data, I see about 13Gb).

That's where the deletion of tables comes handy, because really removes the table from the DB, clearing all the extents of the table. Then do the import of the table, your size should be the same (unless the stoage options have changed in the meantime).

The DB size is the allocated space, not the real occupied space, and if the table is not deleted before an import, there's no optimal re-usage of the allocated space and the DB will add more extents (I used to have problems with Oracle also; after we changed the way of doing things we have no more problems).

I had a quick look today and couldn't figure out how to restore a table rather than a database with Arcserve - so Tavola, maybe Arcserve website is correct?

What we have defintely done before, is have another machine with SQL on and then restore the whole database to that then DTS the 1 table off again to wherever you want it. Not really recommended other than for dire emergencies though!

I'm doing the same thing. A separate machine running SQL 2000 to restore the database. And if I will need the contents of one table I will just DTS. So far I never had any need to do this though. This separate machine could also be handy for my regular dbcheck.

I use raw SQL backup through SQL maintenance. I also have 30gb of database and 4 gb text file. It takes 24 minutes to backup and 6 minutes to verify. Then my Backup Exec copies this into a tape. I could do the dbcheck and the Backup during normal working hours.

Thanks again to all for help.

I've a "live company" of 17GB (SQL), a "test company" of 14GB (SQL) and a "Company 000" of 1.5GB (SQL).
The "live company" dumped is 4.5GB of TXT files (in 8 hours): can I "free" or reorganize the "space lost" (17GB-4.5GB=12.5GB) with a sql or baan function? The Sql maintemaince (optimizing) don't free anything...

the 12.5gb is not entirely the space lost. The empty table alone occupies space plus the fields, the indices and the hashes. definitely you need to re-org your table as part of your baan maintenance. other things you can do on the database are:

- shrinkdatabase
- autoshrink with simple recovery (equivalent to truncate log on checkpoint in SQL 7)
- checkdb to make sure you have no allocation errors. includes checkalloc and checkcatalog.
-frequent transaction logging.
-bulk transfer from old db to new db using MS OLEDB. This procedure clears all error and unused spaces.

Thanks

I read your coment and I've found that the "record" for single row is 8000byte for all table while the effective data are much less (300-2100 byte).
The "Baan Supervisor" (an external technician) advise to not use "auto shrink" on Sql Baan Database because it's not advises by Baan: it's all right?

We do not have the "auto Shrink" option selected but I have shrunk the database using the database maintenance tools.

I would advise against auto shrinking as I have had problems before with locking where a database operation (whether this be a Baan user or a backup) has conflicted with attempting to do database maintenance. For example, once on our test machine, a backup hadn't finished when it was supposed to and this conflicted with shrinking the database through database maintenance. This caused a table to be lost - luckily it was MRP so it could be rebuilt.

I would suggest any database maintenance should be done with all Baan services stopped and making sure a decent backup has completed first.

Tavola,

All the things I suggested are Baan and DB maintenance routines and should be run only when absolutely no users are in the system.