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

  • Article

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

  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

See Also

Tasks

How to: Back Up Database Files and Filegroups (SQL Server Management Studio)

Concepts

Viewing Information About Backups
Full File Backups
Performing File Restores (Full Recovery Model)

Other Resources

BACKUP (Transact-SQL)
RESTORE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance