SQL Server File Backups

By: Greg Robidoux

Overview

Another option for backing up your databases is to use "File" backups.  This allows you to backup each file independently instead of having to backup the entire database.  This is only relevant when you have created multiple data files for your database.  One reason for this type of backup is if you have a very large files and need to back them up individually.  For the most part you probably only have one data file, so this is option is not relevant.

Explanation

As mentioned above you can back up each data file individually.  If you have a very large database and have large data files this option may be relevant.

A file backup can be completed either using T-SQL or by using SSMS.  The following examples show you how to create a transaction log backup.

Create a SQL Server file backup of the TestBackup database

For this example I created a new database called TestBackup that has two data files and one log file.  The two data files are called 'TestBackup' and 'TestBackup2'.  The code below shows how to backup each file separately.

T-SQL

BACKUP DATABASE TestBackup FILE = 'TestBackup' 
TO DISK = 'C:\TestBackup_TestBackup.FIL'
GO

BACKUP DATABASE TestBackup FILE = 'TestBackup2' 
TO DISK = 'C:\TestBackup_TestBackup2.FIL'
GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select either "Full" or "Differential" as the backup type
• Select "Files and filegroups"
• Select the appropriate file and click "OK"

• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\TestBackup_TestBackup.FIL" and click "OK"
• Click "OK" again to create the backup and repeat for other files