How to: Back Up Files and Filegroups (Transact-SQL)

1. To create a file or filegroup backup, use a BACKUP DATABASE <file_or_filegroup> statement. 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.
     

   The basic Transact-SQL syntax for a file backup is:

   BACKUP DATABASE database

   { FILE = logical_file_name | FILEGROUP = logical_filegroup_name } [ ,...f ]

   TO backup_device [ ,...n ]

   [ WITH with_options [ ,...o ] ] ;

   Option	Description
   database	Is the database from which the transaction log, partial database, or complete database is backed up.
   FILE = logical_file_name	Specifies the logical name of a file to include in the file backup.
   FILEGROUP = logical_filegroup_name	Specifies the logical name of a filegroup to include in the file backup. Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup.
   [ ,...f ]	Is a placeholder that indicates that multiple files and filegroups may be specified. The number of files or filegroups is unlimited.
   backup_device [ ,...n ]	Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. To specify a physical backup device, use the DISK or TAPE option: { DISK | TAPE } = physical_backup_device_name For more information, see Backup Devices.
   WITH with_options [ ,...o ]	Optionally, specifies one or more additional options, such as DIFFERENTIAL. A differential file backup requires a full file backup as a base. For more information, see Differential File Backups. Note:

2. Under the full recovery model, you must also back up the transaction log. To use a complete set of full file backups to restore a database, you must also have enough log backups to span all the file backups, from the start of the first file backup. For more information, see How to: Create a Transaction Log Backup (Transact-SQL).

The following examples back up one or more files of the secondary filegroups of the Sales database. This database uses the full recovery model and contains the following secondary filegroups:

• A filegroup named SalesGroup1 that has the files SGrp1Fi1 and SGrp1Fi2.
  
• A filegroup named SalesGroup2 that has the files SGrp2Fi1 and SGrp2Fi2.
  

Note:
Under the full recovery model, regular log backups are necessary to truncate the log and support restoring the database to a specific point in time.

A. Creating a file backup of two files

--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

--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

--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

Tasks

Concepts

Other Resources

Help and Information