1. Partial Backups – for very large databases that contain one or more read-only filegroups, but you want to exclude read-only filegroups
Partial
backups are new in SQL Server 2005 (No partial backup in SQL Server 2000, but partial
restore existed in SQL Server 2000). Although all SQL Server recovery models
support partial backups, partial backups are designed for use under the simple
recovery model to improve flexibility for backing up very large databases that
contain one or more read-only filegroups.
Partial backups 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.
In
short, Partial
backups cover read/-write filegroups
and, possibly, one or more read-only files or filegroups.
Optionally, each partial backup can serve as the base of a series of one or
more differential
partial backups.
--Creating a Partial Backup
BACKUP DATABASE { database_name | @database_name_var }
READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]
TO <backup_device>
[ ,...n ]
[ <MIRROR
TO clause> ] [ next-mirror-to ]
[ WITH {
DIFFERENTIAL | <general_WITH_options> [ ,...n ]
} ]
[;]
2. Partial Restore - for restoring a subset of database
Partial restore operations work with database filegroups. The primary filegroup
is always restored, along with the files that you specify and their
corresponding filegroups. The result is a subset of
the database. Filegroups that are not restored are
marked as offline and are not accessible.
Partial restore operations are accomplished with
the PARTIAL clause of the RESTORE statement. You can also use the PARTIAL
option when restoring a full database backup. Partial database restore
of file backups is not supported.
A partial restore operation is particularly useful
when you are:
·
Creating a subset of a database on another server for
development or reporting purposes.
·
Restoring archived data.
Note: In SQL Server 2000, a partial restore can only be done from a full
database backup. This restriction is removed in SQL Server 2005.
RESTORE DATABASE mywind_part
FILEGROUP =
'sales'
FROM
DISK='g:\mywind.dmp'
WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'g:\mw2.pri',
MOVE 'mywind_log' TO 'g:\mw2.log',
MOVE
'mywind_data_2' TO 'g:\mw2.dat2'
See http://technet.microsoft.com/en-us/library/aa196613(v=sql.80).aspx
for more.
3. Piecemeal Restores – Enhanced version of Partial Database Restore: for databases containing multiple filegroups, bring it online through a piecemeal restore process starting with the primary filegroup
Piecemeal
restore, which is new in SQL Server 2005, enhances Microsoft SQL Server 2000
partial restore (How enhanced? Does partial restore still work independently
in SQL Server 2005?). Piecemeal
restore allows filegroups to be restored after an
initial, partial restore of the primary and some of the secondary filegroups. Filegroups that are
not restored are marked as offline and are not accessible. The offline filegroups, however, can be restored later by a file
restore. To allow the entire database to be restored in stages at different
times, piecemeal restore maintain checks to ensure that the database will be
consistent in the end.
=============
See https://technet.microsoft.com/en-US/library/ms177425(v=SQL.110).aspx
========
This topic is relevant only for databases in SQL Server 2005 Enterprise Edition
and later versions that contain multiple files or filegroups;
and, under the simple mode, only for read-only filegroups.
In SQL Server 2005 and later versions, databases that
contain multiple filegroups can be restored and recovered
in stages through a process known as piecemeal restore. Piecemeal restore
involves a series of restore sequences, starting with the primary filegroup and, in some cases, and one or more secondary filegroups. After the restore sequence is completed, recovered
files, if they are valid and consistent with the database, can be brought
online directly.
Piecemeal restore works
with all recovery models, but is more flexible for the full and bulk-logged
models than for the simple model.
RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1 WITH PARTIAL, NORECOVERY
