Designing a Backup Strategy - Pro SQL Server Internals - page 604

RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:\OrderEntryDB.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:\OrderEntryDB-tail-log.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
RESTORE DATABASE [OrderEntryDB] WITH RECOVERY;
A piecemeal restore greatly improves the availability of the system; however, you should design the data layout
in such a way that allows you to utilize it. Usually, this implies the use of data partitioning techniques, which we
discussed in Chapter 15, “Data Partitioning.”
Partial Database Backup
SQL Server allows you to back up individual files and filegroups as well as excluding read-only filegroups from a
backup. You can back up read-only filegroups separately and exclude them from regular full backups, which could
dramatically reduce the size of backup files and backup time.
Listing 30-21 marks the HistoricalData filegroup as read-only, and it backs up the data from this filegroup.
After that, it performs a full backup for read-write filegroups only using the READ_WRITE_FILEGROUPS option and
log backup.
Listing 30-21. Partial backup: Performing backups
alter database [OrderEntryDB]
modify FILEGROUP [HistoricalData] readonly
go
BACKUP DATABASE OrderEntryDB
FILEGROUP = N'HistoricalData'
TO DISK = N'V:\OrderEntryDB-hd.bak'
WITH NOFORMAT, INIT,
NAME = N'OrderEntryDB-HistoricalData Backup', STATS = 5;
BACKUP DATABASE OrderEntryDB READ_WRITE_FILEGROUPS
TO DISK = N'V:\OrderEntryDB-rw.bak'
WITH NOFORMAT, INIT,
NAME = N'OrderEntryDB-Full', STATS = 5;
BACKUP LOG OrderEntryDB
TO DISK = N'V:\OrderEntryDB.trn'
WITH NOFORMAT, INIT,
NAME = N'OrderEntryDB-Transaction Log ', STATS = 5;
You can exclude HistoricalData filegroup from all further full backups as long as you keep them read-only.
If you need to restore the database after a disaster, you could perform a piecemeal restore of read-write
filegroups, as shown in Listing 30-22.