PARTIAL Backup in SQL Server Step by Step Tutorial with Examples

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).

This is Part 15 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

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.

Related Articles

• SQL Server WHERE Clause T-SQL Tutorial with Examples
• SQL Server ORDER BY Clause T-SQL Tutorial with Examples
• SQL Server SELECT TOP Clause T-SQL Tutorial with Examples
• SQL SELECT | SQL QUERY | SQL SELECT Statement T-SQL Tutorial with Examples
• SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
• FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
• ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS SQL Server T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples