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 Partial Backups



By:
Overview

A new option is "Partial" backups which was introduced with SQL Server 2005.  This allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files.  This is a good option if you have Read-Only filegroups in the database and do not want to backup the entire database all of the time.

Explanation

A Partial backup can be issued for either a Full or Differential backup.  This can not be used for Transaction Log backups.  If a filegroup is changed from Read-Only to Read-Write it will be included in the next Partial backup, but if you change a filegroup from Read-Write to Read-Only you should create a filegroup backup, since this filegroup will not be included in the next Partial backup.

A partial backup can be completed only by using T-SQL.  The following examples show you how to create a partial backup.


Create a SQL Server partial backup of the TestBackup database

For this example I created a new database called TestBackup that has three data files and one log file.  Two data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup.  The code below shows how to do a partial backup.

T-SQL

Create a SQL Server full partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS
TO DISK = 'C:\TestBackup_Partial.BAK'
GO

Create a SQL Server differential partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS
TO DISK = 'C:\TestBackup_Partial.DIF'
WITH DIFFERENTIAL
GO

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.



    



Friday, June 24, 2016 - 5:32:55 AM - Rohit Back To Top

Hi,

I want to understand the difference between Partial and filegroup backup. 

 


Learn more about SQL Server tools