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'
GOSQL 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
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
