Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 333

13. Using the following statement, let us restore database pages as a piecemeal process:
RESTORE DATABASE PieceMealDB
PAGE='1:18,2:24,3:36'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\Backup\PieceMealFullDatabaseBackup.bak' WITH
NORECOVERY, REPLACE
GO
14. Now, restore the transaction log using the following statement:
RESTORE LOG PieceMealDB
FROM DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\Backup\PieceMeal_TlogAfterBackup.bak' WITH
RECOVERY;
15. Now, we have finished the process to restore a page to recover data pages.
This completes the required steps to implement piecemeal restore strategies using the online
page restore feature.
How it works...
The piecemeal restore process involves a two-fold step—offline piecemeal and online
piecemeal scenario.
F Offline piecemeal scenario: In the offline piecemeal restore scenario, the database
is online after the partial restore sequence. The filegroups that have not yet been
restored will remain offline and the relevant filegroups can be restored as and when
they are required.
F Online piecemeal scenario: In the online piecemeal restore scenario, after the
partial restore sequence, the database is online and the primary filegroup and all
remaining filegroups are available. The filegroups that have not yet been restored will
remain offline, but they can be restored while the database remains online.
In this recipe, the PieceMealDB database was restored from a full backup restoring the
PRIMARY filegroup and the transaction logfile. We have used, WITH clause that includes,
PARTIAL keyword and the NORECOVERY clause, which enables the transaction log backups
to be restored. Once the transaction log restore step is complete, the database is partially
available where the PRIMARY filegroup is available and all other objects that are stored in
SECONDARY and TERTIARY filegroups. This process is very helpful to manage very large
databases using the PARTIAL keyword during a data recovery operation that allows the users
and application with initial database connectivity; while the DBAs prioritize the steps to load
remaining filegroups that will have higher priority, making them available sooner.
In the next set of steps, we have successfully restored the single data-pages from a full
database backup and corresponding transaction log backup files, which is similar to usual
RESTORE DATABASE commands.