Backup and recovery concepts Recovery considerations for files and filegroups
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 do place a table into a different filegroup than one of its indices and use NetBackup for SQL Server to back it up, you may fail with the following SQL Server error message:
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.
Recovery considerations for files and filegroups
To ensure that you will be able to successfully restore a database from file and filegroup backups, it is important to always have backups of a full set of files and filegroups that constitute the entire database, as well as transaction log backups that span the entire period of time over which the backups were taken. To maintain an unbroken sequence of transaction log backups, it is essential to perform a transaction log backup following every file or filegroup backup. (If you back up several files or filegroups at once, then you only need to back up the transaction log after the last such backup.) If the transaction log is not backed up, SQL Server will not allow you to restore a file or filegroup.
SQL Server does not keep a record in the transaction log of new files or filegroups that are created. Therefore, after you add either a file or a filegroup to the database, you must immediately back it up. Then perform a backup of all the filegroups in the database so NetBackup will select the correct recovery set when subsequent backups are performed. Similarly, after creating a database file, you should back up all of the files in the filegroup to which it belongs.