Back Up Files and Filegroups

Artikel
02/28/2023

Applies to: SQL Server

This topic describes how to back up files and filegroups in SQL Server by using SQL Server Management Studio, Transact-SQL, or PowerShell. 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 (SQL Server) and Differential Backups (SQL Server).

Before You Begin

Limitations and Restrictions

The BACKUP statement is not allowed in an explicit or implicit transaction.
Under the simple recovery model, read/write files must all be backed up together. This helps make 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, see Partial Backups (SQL Server).

For more information about limitations and restrictions, see Backup Overview (SQL Server).

Recommendations

By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries, see Trace Flags (Transact-SQL).

Permissions

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

Using SQL Server Management Studio

After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
In the Database list, verify the database name. You can optionally select a different database from the list.
In the Backup type list, select Full or Differential.
For the Backup component option, click File and Filegroups.
In the Select Files and Filegroups dialog box, select the files and filegroups you want to back up. You can select one or more individual files or check the box for a filegroup to automatically select all the files in that filegroup.
Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
(optional) In the Description text box, enter a description of the backup set.
Specify when the backup set will expire:
- To have the backup set expire after a specific number of days, click After (the default option) and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.
  
  The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this option, right-click the server name in Object Explorer and select properties; then select the Database Settings page.
- To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.
Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives that contain a single media set, click Add. The selected paths are displayed in the Backup to list.

Note

To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.
To view or select the advanced options, click Options in the Select a page pane.
Select an Overwrite Media option, by clicking one of the following:
- Back up to the existing media set
  
  For this option, click either Append to the existing backup set or Overwrite all existing backup sets.
  
  For information about backing up to an existing media set, see Media Sets, Media Families, and Backup Sets (SQL Server).
  - (optional) Select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
  - (optional) Enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name that you enter here.
  If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.
- Back up to a new media set, and erase all existing backup sets
  
  For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.
  
  For more information about creating a new media set, see Media Sets, Media Families, and Backup Sets (SQL Server).
(optional) In the Reliability section, check:
- Verify backup when finished.
- Perform checksum before writing to media, and (optional) Continue on checksum error.
  
  For more information about checksums, see Possible Media Errors During Backup and Restore (SQL Server).
If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option enables the Rewind the tape before unloading option.

Note

The options in the Transaction log section are inactive unless you are backing up a transaction log (as specified in the Backup type section of the General page).
SQL Server 2008 (10.0.x) Enterprise and later versions support backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

To view the current backup compression default, see View or Configure the backup compression default Server Configuration Option

Using Transact-SQL

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 (SQL Server).
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 Create a Differential Database Backup (SQL Server).

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 Back Up a Transaction Log (SQL Server).

Examples

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.

A. Create 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. Create 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. Create 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'  
   WITH   
      DIFFERENTIAL;  
GO

Using PowerShell

Set up and use the SQL Server PowerShell Provider.

Use the Backup-SqlDatabase cmdlet and specify Files for the value of the -BackupAction parameter. Also, specify one of the following parameters:

To back up a specific file, specify the -DatabaseFileString parameter, where String is one or more database files to be backed up.
To back up all the files in a given filegroup, specify the -DatabaseFileGroupString parameter, where String is one or more database filegroups to be backed up.

The following example creates a full file backup of every file in the secondary filegroups 'FileGroup1' and 'FileGroup2' in the <myDatabase> database. The backups are created on the default backup location of the server instance Computer\Instance.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database <myDatabase> -BackupAction Files -DatabaseFileGroup "FileGroup1","FileGroup2"

Delen via