Backing up sql filegroups

[hammamet]

On sql 2000 using BackupExec v9.0 rev 4454, I'm trying to backup up very large sql db's (251 GB)but because of the time it takes to restore we're now looking at backing up the filegroups. However, when we tried a filegroup backup we received the following error message:

A SQL Server filegroup was selected along with the LOG backup method . Log backups can only be performed on entire databases, not on individu al filegroups.

The plan that we had to test a recovery situation was to:

Full backup of filegroups
TLog backup 1
Manipulate the data
TLog backup 2
More data manipulation
TLog backup 3
Mangle database
TLog backup 4 - no truncate option

then try to recover the database at the point closest to when they went wrong by:
Restore full backup of filegroups - no recovery
Restore differetial backup of filegroups - no recovery
Restore TLog backup 3 - no recovery
Restore TLog backup 4 - with recovery

The above is useless as we can't even get started on it. If anyone can give some help on this it would be greatly appreciated.

 

[SQLBill]

You say "A SQL Server filegroup was selected along with the LOG backup method". That appears to mean that you are trying to do both with the same job. Can't be done. You must do them separately.

For more reference...on the SQL Server itself, check out the Books OnLine (Start>Programs>Microsoft SQL Server>Books OnLine), use the Index tab and enter BACKUP DATABASE. Choose the option for TRANSACT SQL. Scroll down to Backup Types and see what it says about File backups and Transaction Log backups.

-SQLBill

 

[Zelandakh]

Backup the database to disk using SQL. If the disk is fast enough and you back up to a different fast disk it should be ok.
Then get BE to backup the data file/s to tape.

Double safety - can restore from disk on corruption. Can restore from tape for server loss.

 

[hammamet]

Thanks for that SQLBill - we'll start to have a look at what you suggest.

Zelandakh, thanks also for your reply - that's the way we currently run the backups but we find it takes too long to restore (even to fast drives) and because of the length of time we often loose network connections, plus there are problems finding windows in which to run the backup so we're trying to reduce what we backup. I know it's meant to run in the background without affecting any processes that are running, but that's not always been our experience.