Export (0) Print
Expand All

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.

ms179401.note(en-US,SQL.90).gifImportant:
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.

  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.
    ms179401.note(en-US,SQL.90).gifNote:

  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.
ms179401.note(en-US,SQL.90).gifNote:
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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft