File backup & Filegroup backups

The files in a SQL Server database can be backed up and restored individually. Also, you can specify a whole filegroup instead of specifying each constituent file individually. 

/**** TSQL- The following examples demonstrates how to create the file-level backup of the files:

BACKUP DATABASE SQLShackFileBackup  

   FILE = 'SQLShackFileBackup_1',   

   FILE = 'SQLShackFileBackup_2'   

   TO DISK = 'f:\PowerSQL\SQLShackGroupfiles.bak';  

/**** TSQL- The following examples illustrates the full file backup of all the files in both of the primary and secondary file-groups.

BACKUP DATABASE SQLShackFileBackup  

   FILEGROUP = 'PRIMARY',

   FILEGROUP = 'Secondary' 

   TO DISK = 'f:\PowerSQL\SQLShackGroupfilegroup.bak';  

Partial backups

           A new option is "Partial" backups which was introduced with SQL Server 2005.  This allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files.  This is a good option if you have Read-Only filegroups in the database and do not want to backup the entire database all of the time.

The READ_WRITE_FILEGROUPS option is used with the BACKUP DATABASE command. This command is for partial backup. It processes the backup of read-write file groups.

/**** TSQL-Create a SQL Server full partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS 

TO DISK = 'C:\TestBackup_Partial.BAK'

GO

/**** TSQL-Create a SQL Server Diffential partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS

TO DISK = 'C:\TestBackup_Partial.DIF'

WITH DIFFERENTIAL

GO