SQL Backups (Table level)

[rbogle]

Just found this forum today. My company has been using Arcserve to backup up several NT servers running SQL Server for a while now. I, however, am new to Arcserve. We're using the SQL add-on to get the open sql files. Here's my question...

Is anyone out there doing table level backups (SQL Server)? Is this the only way to backup and restore a table without restoring the entire database? Is there a way to configure Arcserve to backup ALL tables dynamically (without having to manually configure Arcserve each time a new table is added to the database)?

Thanks

 

[Ivanhoe]

Hi'

I use the ASO version of ARCserve 6.61 and i don't see that you can do table level backup at all.
Only complete, differential, transaction log and files and/or filegroups (wich i never used, but i don't see it as table level backup).

So restore is the entire database or transaction log, as i see it.

s-)

 

[pjh1709]

Hi, I have used the following, from

http://support.cai.com/techbases/asnt/16051.html

Hope it helps.
Regards,
Phil.

Title: How to restore a SQL table using ARCserve 6.5 for Windows NT
Product: ARCserve 6.5 for Windows NT
Document Number: 16051
Date Updated: February 2, 1998

Pre-requisites:

To restore individual tables to a SQL database, you must have SQL version 6.5, Arcserve 6.5 for NT, and the latest SQL agent (at this time, version 2.0 build 61).
The tables must have been backed up individually. To do so, open up the Microsoft SQL Server icon in a backup source screen, open up the database, and select any or all of tables to be backed up.
As always, verify that your backups have been successful by viewing the Arcserve activity log. In addition, test restores should be run periodically.

To restore:
Launch the SQL Enterprise Manager, open up the databases icon, open the database in question, click on the + sign next to objects, and open the tables icon.
Highlight the table that you are going to restore, right click on it and go to generate SQL scripts.
Accept all the defaults and click on the script button. In the filename box, name the script anything you would like, but make sure it has a .SQL extension. Click on the save button. After you get the scripting successfully completed message, you can close the script box.
Next highlight the table, right click on it and rename it to the original tablename and add "old" to the end of the name. Example: if it was originally test, it will now be testold.
After you have renamed the table, look for the SQL query tool on the tool box-the icon has 2 boxes and a ?. Click on the query tool and then click on the folder icon, which is the load .SQL script icon. Browse for your saved script and then click on the open button. This will show you the table properties. To execute the query, hit the green arrow icon. You will get a result that says that it did not return data or rows. This is OK. You can now close the query window.
At this point you have created a table template. From the Enterprise manager, go to the database this table belongs to and double click on it. To do the restore, you must select into/bulk copy under the options tab.
Launch the Arcserve manager and go to a restore screen.
Change your restore view to from restore by tree to restore by session, look for the tape the table was backed up to, open the appropriate session, browse for the table name and select it by filling in the green box next to it. Note: it will be called the database name.table name(dbo). Under the destination tab, make sure restore to original location is selected. You can not perform a redirective restore-the table must be restored to the original database. Submit the restore job.
After the restore is finished, view the Arcserve log to make sure the restore was successful.
If the restore has been successful, stop and re-start the SQL services.
Note 1:
If you have lost the table and cannot run the SQL script, recreate the table with the exact name prior to restoring.
Note 2:
A full database backup should follow a table restore. A table restore disables the dump transaction until a full database backup is done.
Note 3:
The following information has been taken from the SQL on-line help.
Important: Only a full database or transaction log backup ensures that all PRIMARY KEY to FOREIGN KEY constraints are correct.

Table backup and restore operations have the following characteristics:

Table restores can be performed from individual table backups.

Table restores cannot be performed for tables that have text or image columns or that are published for replication.

Triggers, rules, defaults, and declarative referential integrity (DRI) are not enforced for table restores.

Table restores can be performed only when the Select Into/Bulk Copy database option is set.

A table restore disables the DUMP TRANSACTION statement. To enable the DUMP TRANSACTION statement again, you must back up the database only after the restore.

A table backup and restore is not supported if it is indexed.

 

[monkeylizard]

Here's the real deal on your SQL restores.

Do a complete restore of the database (the only option AS6.61 gives you) but restore it under a different database name. Do this by right clicking on the Database in the Restore By Tree view and selecting the SQL Backup Agent Options. From teh new window that appears, you can change the name of the database by double clicking on the path that appears in the center of the new window. You'll get a cursor in the path (hard to see sometimes) and you can simply change the name of the database.

After that, use SQL to transfer tables from the restored database (ie DBASE_BAK) to the original (ie DBASE)

Hope this helps... Monkeylizard
-Isaiah 35-

 

[lovalles]

I have Arcserve 2000 and using Agent, but i just cant open the database to see the tables, i am trying to use by session backup or by tree and still getting no level only the database?

whats wrong?