SQL PARTIAL

What is the SQL PARTIAL keyword used for?

PARTIAL tells SQL Server to back up or restore only the primary and all read-write filegroups instead of the entire database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PARTIAL: Supported: Microsoft SQL Server (2005 and later) | Not supported: PostgreSQL, MySQL, Oracle, SQLite, Snowflake

SQL PARTIAL Full Explanation

In Transact-SQL, the PARTIAL keyword is an option used with BACKUP DATABASE and RESTORE DATABASE statements. A partial backup contains the primary filegroup and every read-write filegroup but excludes read-only filegroups. Likewise, a partial restore brings back just those filegroups, letting large databases be protected or recovered faster when read-only data can be recreated or is stored elsewhere.Behavior- Backup: When PARTIAL is specified (usually together with READ_WRITE_FILEGROUPS), SQL Server skips read-only filegroups, producing a smaller backup file and shorter run time.- Restore: RESTORE DATABASE ... PARTIAL rebuilds the database with only the backed-up read-write portions plus the primary filegroup. Read-only filegroups can be restored later if needed with RESTORE FILEGROUP.Caveats- Supported only in the FULL or BULK_LOGGED recovery models.- Cannot be combined with DIFFERENTIAL in the same statement (use separate differential partial backups instead).- Not available for system databases.- You must include the PRIMARY filegroup in every partial backup.- After a partial restore, the database is left in a partially restored state; additional restores may be required before recovery (ROLLBACK) is possible if read-only filegroups are still needed.

SQL PARTIAL Syntax

-- Create a partial backup
BACKUP DATABASE database_name
  READ_WRITE_FILEGROUPS
  TO DISK = 'C:\Backups\database_rw.bak'
  WITH PARTIAL;

-- Restore from a partial backup
RESTORE DATABASE database_name PARTIAL
  FROM DISK = 'C:\Backups\database_rw.bak'
  WITH NORECOVERY;

SQL PARTIAL Parameters

Example Queries Using SQL PARTIAL

-- Partial backup example
BACKUP DATABASE SalesDB
  READ_WRITE_FILEGROUPS
  TO DISK = 'D:\bkups\SalesDB_partial.bak'
  WITH PARTIAL, INIT, STATS = 5;

-- Partial restore example
RESTORE DATABASE SalesDB PARTIAL
  FROM DISK = 'D:\bkups\SalesDB_partial.bak'
  WITH MOVE 'SalesDB_Data' TO 'E:\Data\SalesDB.mdf',
       MOVE 'SalesDB_Log'  TO 'E:\Logs\SalesDB.ldf',
       NORECOVERY;

-- Bring database online after restoring remaining filegroups
RESTORE DATABASE SalesDB WITH RECOVERY;

Expected Output Using SQL PARTIAL

The BACKUP statement creates a
bak file that contains only the primary and read-write filegroups
The RESTORE statement re-creates those filegroups and leaves the database waiting for additional restores (NORECOVERY) or ready for use (after RECOVERY)

Use Cases with SQL PARTIAL

Speed up backups for very large databases where read-only archives seldom change.
Reduce storage costs by omitting static historical data from frequent backups.
Perform filegroup-level recovery after accidental data changes restricted to read-write portions.
Stage databases in development or test environments without copying terabytes of read-only data.

Common Mistakes with SQL PARTIAL

Forgetting to include READ_WRITE_FILEGROUPS, resulting in a full backup instead of partial.
Assuming PARTIAL also backs up read-only filegroups.
Attempting to use PARTIAL on system databases like master or msdb.
Mixing PARTIAL with incompatible options such as COPY_ONLY incorrectly.
Trying to restore a partial backup with RECOVERY before restoring required log backups.

First Introduced In

SQL Server 2005

Frequently Asked Questions

What is a partial backup?

A partial backup contains only the primary and all read-write filegroups of a database, omitting read-only data. It is created with the PARTIAL option.

When should I use RESTORE PARTIAL?

Use RESTORE PARTIAL when you only need to recover the primary and read-write filegroups, such as after accidental data changes or corruption that did not affect read-only data.

Do I still need log backups after a partial backup?

Yes. To remain in a recoverable state under the FULL or BULK_LOGGED recovery models, you must continue taking log backups and restore them after a partial restore.

Can I combine PARTIAL with COPY_ONLY?

Yes. COPY_ONLY can be added to create an out-of-band partial backup that does not affect the backup chain, for example:BACKUP DATABASE MyDB READ_WRITE_FILEGROUPS TO DISK='file.bak' WITH PARTIAL, COPY_ONLY;