File and filegroup backups

A file backup contains all the data in one or more files (filegroup). This backup can be performed when the database size and performance requirements make a full database backup impractical.

Minimally, this statement must specify the following:

• The database name.
• A FILE or FILEGROUP clause for each file or filegroup, respectively.
• The backup device on which the full backup will be written.

So long as you do not need to restore the priary filegroup and you are running sql server 2008 enterprise, the database can remain online and accessible to applications during the resotre operation. Only theportion of the database being restored is off line.

Partial Backup
To reduce the size of a backup to only the filegroups that can change, you can perform a partial backup. Patrital backps are performed by specifying the READ_WRITE_FILEGROUPS option as follows

BACKUP DATABASE DBNAME READ_WRITE_FILEGROUPS [filegrouplist] TO DEVICE

When a partial backup is executed, SQL SErver backs up the primary filegroup, all read/write filegroups, and any explicitly specified read only filegroups

Examples of filegroup backups

A. Creating a file backup of two files

The following example creates a differential file backup of only the SGrp1Fi2 file of the SalesGroup1 and the SGrp2Fi2 file of the SalesGroup2 filegroup.

--Backup the files in the SalesGroup1 secondary filegroup.
BACKUP DATABASE Sales
   FILE = 'SGrp1Fi2',
   FILE = 'SGrp2Fi2'
   TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck'
GO

B. Creating a full file backup of the secondary filegroups

The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GO

C. Creating a differential file backup of the secondary filegroups

The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GO
   WITH
      DIFFERENTIAL,
GO

Source: http://msdn.microsoft.com/en-us/library/ms179401.aspx