mssqltips logo

SQL Server File Backups



By:
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 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.






download





get free sql tips

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