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

5. As best practice, perform a full database backup:

BACKUP DATABASE [PieceMealDB] TO DISK = N'C:\Program Files\
Microsoft SQL Server\MSSQL10_50.SQL2K8R2U\MSSQL\Backup\
PieceMealFullDatabaseBackup.bak' WITH NOFORMAT, INIT, NAME =
N'PieceMealDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
STATS = 10, CHECKSUM
GO

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N'PieceMealDB' and backup_set_id=(select max(backup_
set_id) from msdb..backupset where database_name=N'PieceMealDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup
information for database ''PieceMealDB'' not found.', 16, 1) end

6. Check the backup file consistency:

RESTORE VERIFYONLY FROM DISK = N'C:\Program Files\
Microsoft SQL Server\MSSQL10_50.SQL2K8R2U\MSSQL\Backup\
PieceMealFullDatabaseBackup.bak' WITH FILE = @backupSetId,
NOUNLOAD, NOREWIND
GO

How to do it...

The process is two-fold:

F Using PARTIAL command with the RESTORE DATABASE command
F Restoring the corrupted pages (piecemeal restore)

The following steps are needed to implement the piecemeal restore strategies using the
online page restore feature:

1. In this recipe, the PieceMealDB is used to restore from a full database using

the PARTIAL keyword, and we will bring only PRIMARY filegroup online by leaving
filegroups SECONDARY and TERTIARY .

2. Initially, we will perform a transaction log to capture the latest activities from the last

database backup:
BACKUP LOG [PieceMealDB] TO DISK = N'C:\Program Files\
Microsoft SQL Server\MSSQL10_50.SQL2K8R2U\MSSQL\Backup\PieceMeal_
TlogAfterBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'PieceMealDB-
Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10,
CHECKSUM
GO