SQL backup

[hammamet]

Does anyone know of any backup software that allows you to backup tables in sql server, rather than the entire database? We're currently backing up databases of >200gb, which can take over 24 hours to restore when all that's needed is a table. If a restore is needed it's usually time critical, but because we run so many backups (1.3gb over the week end, split into 8 jobs), we also need a solution that doesn't need a lot of managing.

 

[SQLBill]

Can't do it. SQL Server doesn't allow that and every backup software that backs up SQL Server uses the SQL Server commands.

-SQLBill

 

[SQLBill]

I should have said that 'every backup software that I'm aware of, ...'

-SQLBill

 

[justin2000]

Veritas used to have a product called Edition for SQL server - it allowed you to do snapshots of the SQL database and then use the snapshot to restore or backup the snapshot itself.
Not sure what happened to it though.

Thinking you can probably do the same sort of thing with VSS in windows 2003 - dunno how though - but I am sure there is some software vendor out there that does it.

That being said, what type of tape drive are you using? Ever thought of getting a higher speed tape drive to do the job?

 

[hammamet]

Thanks for your responses. We're currently using a mix of AIT 2 and 3, but part of the problem is finding a server with enough spare space to restore a large databse to, which is why we would be keen on just being able to restore a table.

 

[SQLBill]

SQL Server doesn't allow object (table) backup and restore. It's the whole database or nothing. (I'm a SQL Server DBA and I sure wish there was a way to do table backup, but there isn't).

-SQLBill

 

[hammamet]

Thanks for that but how would you manage large sql db backups? I know that you can backup file groups but it seems a lot of managing and I'm not sure that it would be any more time/energy efficient.

 

[SQLBill]

I have a database with 209+GB of data. I back it up to disk (using SQL Server backup commands), then use Veritas to copy the backup files to tape. I'm using LTO 100/200 tapes. With compression, I get a full backup onto two tapes.

-SQLBill

 

[Scanner]

Have you looked into Filegroups? This is a method used by Veritas to archive large databases (usually enterprise-level). It works especially well when the database is in a clustered environment.

It adds to the overhead of management because of the need to define the filegroups and the individual jobs created to archive them, but it might be what you're looking for.

Here's a link to a white paper authored by Veritas (yet not on V-tas' site). It has much better information on "filegroups" than I can contribute right now:

http://www.dlt.com/storage/PDF/whitepapers/veritas/dp/be_sql_wp_final.pdf

Good luck, and keep us posted.

Scanner

 

[RobertMottram]

Could you use a DTS script in SQL to dump the data in the one table you need to a file then put that file onto tape? This script could be scudualed to run before a nightly backup.
This would not replace the need for a full backup of the database, but if you generaly need to restore the same table it would give you the ability to get to that table

 

[hammamet]

Unfortunately we never know which table is going to need to be restored and we're backing up 100+ databases a night - much more at the week end. So really what would be wonderful is something that backed up the database and everything in it, dts jobs, tables etc and allowed you to restore these things separately. Crying for the moon I know, but we can dream?

At the moment we are using a mixture of AIT2 and 3 autoloaders, running some backups straight to tape and others to hard drive and then to tape, but of course, the latter strategy increases the time for a restore. We're looking at going over to dlt as there seems to be some opinion that it's more reliable.

Many thanks to eveyone who has responded, it's been a help to know that it's not just me.

 

[kainite]

You could of course revert back to SQL Server 6.5 because you could do table only restores in 6.5 .... that's progress isn't it!

Best solution I have found is to have a low spec big disk server (i.e. not fancy/costly disks just "normal" PC ones) that has buckets of space and restore to that then copy back individual tables

 

[vm146971]

You might try SQLLite speed, it won't help with he table space restore , but it can compress theSQL backup to disk down 1 1/3 original size, and speeds up the backup and restore process a lot, plus you use a lot less tapes.