MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

















Trending SQL Server Tips

PARTIAL Backup in SQL Server Step by Step Tutorial with Examples

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: TAIL-LOG Backups in SQL Server

Page 15 / 16


Click here to read this tutorial from the beginning...

Introduction

As per MSDN "PARTIAL Backups are designed for use under SIMPLE recovery model; thereby to improve flexibility to backup very large database which contain one or more read-only Filegroups. They are useful whenever you want to exclude read-only Filegroups. A partial backup resembles a full database backup, but a PARTIAL backup does not contain all the Filegroups. Instead, for a read-write database, a partial backup contains the data in the primary FileGroup, every read-write FileGroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary FileGroup." For more information, see Partial Backup (SQL Server).

In the previous example we created a database named MyTechMantra which had three data files and one transactional log file. Two of the data files are residing in PRIMARY FileGroup and one data file was residing in a READ-ONLY FileGroup. Now to create a FULL PARTIAL Backup use the below TSQL script.

Create a FULL PARTIAL backup Using TSQL command

BACKUP DATABASE [MyTechMantra] READ_WRITE_FILEGROUPS
TO DISK = 'C:\DBBackups\MyTechMantra_Partial.BAK'
WITH NOFORMAT, NOINIT, COMPRESSION, STATS = 25
GO

Create a DIFFERENTIAL PARTIAL backup Using TSQL command

BACKUP DATABASE [MyTechMantra] READ_WRITE_FILEGROUPS
TO DISK = 'C:\DBBackups\MyTechMantra_Partial.DIF'
WITH DIFFERENTIAL, COMPRESSION, STATS = 25
GO


Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Previous Page.. Next Page..


Learn More...




Last Updated On: Feb 12, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: