File Backups

SQL Server allows to make backups and restore not only the entire database but also a single filegroup or even a separate file.  This is called a file backup. This type of backup contains all data from one or more files or filegroups.

File backups are often used to increase the recovery process speed by restoring only damaged files, without restoring the rest of the database, if necessary. Assume there is a database that contains several files. Each file is stored on a separate disk and suddenly one disk fails. In this case the recovery process will not take more time because there is no need to restore the entire database, it will be enough to restore only the file from the failed disk.

Another use case is when some filegroups in your database are updated more frequently than others. In this case you can backup them separately using a file backup.

One of the biggest disadvantages of using file backup is that such backups are difficult to manage. It takes more time to manage every file backup separately. Also, if failure occurs and the backup of the damaged file is lost the entire database becomes unrecoverable.

Let’s explore how does full file backup work under different recovery models. Assume there is a database “Adventureworks” which contains two filegroups “Group1” and “Group2”, each filegroup includes two files “File1” and “File2” for filegroup “Group1” and “File3” and “File4” for “Group2”.

Case for Simple Recovery Model

To make sure that the database can be restored, under the simple recovery model,  all read-write files must be backed up together. It’s quite simple to accomplish this as there is no need to backup all read-write files one by one, you just need to use the READ_WRITE_FILEGROUPS option (known as a partial backup):

BACKUP DATABASE Adventureworks READ_WRITE_FILEGROUPS, 
   FILEGROUP = 'Group1', 
   FILEGROUP = 'Group2' 
TO DISK = 'Groups.bck'

Case for Full Recovery Model

If the database runs under the full recovery model you should backup the transaction log, regardless of the file backup strategy you chose. All transaction log backups beginning with the first file backup and a complete set of  full file backups equal to a full database backup. Please check the picture bellow to understand how it works:

BACKUP LOG Adventureworks TO DISK = 'log_13_00.bak' 
BACKUP DATABASE Adventureworks FILEGROUP = 'Group2' TO DISK = 'Group2.bck' 
BACKUP LOG Adventureworks TO DISK = 'log_14_00.bak' 
BACKUP DATABASE Adventureworks FILEGROUP = 'Group1' TO DISK = 'Group1.bck'
BACKUP LOG Adventureworks TO DISK = 'log_15_00.bak' 
BACKUP DATABASE Adventureworks FILEGROUP = 'Group3' TO DISK = 'Group3.bck'

Note though, that using this backup strategy leads to a complex database restoration process.

If any file is offline then the whole filegroup containing this file is offline too and cannot be backed up.

SQL Server backups only one file at a time. Of course, it is possible to backup multiple files in one operation, but should you need to restore only one file, the restore operation will take more time (to find the file, the whole backup will be read).

Case for Differential File Backup

SQL Server allows to make not only full file or filegroup backups but also a differential file backup. Creating a full file backup makes it possible to apply a differential file backup. If the database runs under the simple recovery model the differential file backup can be applied only to read-only filegroups. As far as the full recovery model is concerned, a differential file backup can be applied to any filegroup which has been previously backed up. Differential file backups reduce time to restore database because less transaction log backups have to be restored. It is better to use differential file backups under the following circumstances:

• There is a need to backup some files more frequently than other
• Some files are really large and changes in these files occur infrequently

Making a differential file backup is quite simple, just add “WITH DIFFERENTIAL” option:

BACKUP DATABASE Adventureworks 
   FILE = 'File2',
   FILE = 'File3' 
TO DISK = 'Groups_diff.bck' WITH DIFFERENTIAL

How to Create a File Backup with SQL Server Management Studio

Here is how you can create a file backup using SSMS:

1. Right click on the database you want to backup
2. Select “Tasks”, then “Back up…”
3. Choose “Files and Filegroups” and in the new window check in the files or file groups you want to backup
4. Add a backup destination
5. Click “OK”