• Article

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

When the database size and performance requirements make a full database backup impractical, you can create a file backup instead. A file backup contains all the data in one or more files (or filegroups). For more information about file backups, see Full File Backups and Differential File Backups.

Important

Under the simple recovery model, read/write files must all be backed up together. This makes sure that the database can be restored to a consistent point in time. Instead of individually specifying each read/write file or filegroup, use the READ_WRITE_FILEGROUPS option. This option backs up all the read/write filegroups in the database. A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup. For more information, see Partial Backups.

To back up files and filegroups

  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.

    NoteNote
    A differential file backup requires a full file backup as a base. For more information, see Differential File Backups.

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

Example

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

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