Veritas NetBackup™ for Microsoft SQL Server Administrator's Guide

Protecting SQL Server files and filegroups

If your plan to protect SQL Server includes backups of files and filegroups, then the database must use the full or bulk-logged recovery model. In addition, you must maintain the unbroken sequence of transaction log backups. You must create the files and filegroups for your databases and place individual database components into them. NetBackup places a restriction on the layout of your database so it can successfully perform backups and restores of database files and filegroups.

For file or filegroup backups, ensure that a table and its indices reside within the same filegroup.

For example, the layout as indicated by the following Transact SQL statements should not be used:

use master
CREATE DATABASE MultiFileDB
ON
PRIMARY ( NAME = FileX,
          FILENAME = 'd:\mssql\data\FileX.mdf'),
FILEGROUP AltGroup
( NAME = AltGroupFil,
  FILENAME = 'd:\mssql\data\AltGroupFil.ndf') 
GO
use MultiFileDB
CREATE TABLE Table1 (col1 char(10),col2 char(10), col3 char(10)) on AltGroup
go
create unique clustered index index4 on Table1 (col2)
go

Notice in this example, Table1 has been placed in filegroup AltGroup but its index is placed (by default) in the primary filegroup.

If you place a table into a filegroup that is different than one of its indices, the backup may fail. The following SQL Server error message is displayed:

Database file <file name> is subject to logical recovery and 
must be among the files to be backed up as part of the file 
or filegroup backup.