Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server File Backups


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.


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.


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

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

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 file to backup
  • 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
file backup using ssms

Last Update: 3/12/2009

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools