Transcript Database Mirroring: Maximizing Availability Through Automation

Replication Magic:
Initializing From Backup
Robert L Davis
Who am I?
Replication Magic: Initializing from Backup




Background
Use Cases & Misconceptions
Initializing from Log Backups
Initializing from Filegroup Backups
Replication Magic: Initializing from Backup
 Background
 Introduced in SQL Server 2005
 Limited adoption for VLDBs due to lack of native
compression
 Should be widely used for VLDBs
 Highly flexible
Replication Magic: Initializing from Backup
 Use Cases & Misconceptions
 VLDBs
 Myth: Cannot use compression in SQL Server 2005
 Myth: Must initialize with a full backup
 Partial Database Replication
 Myth: Must restore the whole database and then
delete unwanted tables
Replication Magic: Initializing from Backup
 Initializing from Log Backups
 Use case: set up log shipping then convert to
transactional replication
 Books Online correctly states:
“… any recent backup can be used if it was taken after the
publication was enabled for initialization with a backup.”
Replication Magic: Initializing from Backup
 Initializing from Filegroup Backups
 Replicating select tables
 Books Online incorrectly states:
“A backup contains an entire database; therefore each
subscription database will contain a complete copy of the
publication database when it is initialized”
 Can restore simply select filegroups
 Partial database availability is an Enterprise-only
feature
Replication Magic: Initializing from Backup
Replication Magic:
Initializing from Backup
Q&A
Replication Magic:
Initializing from Backup
Thank You!
Slide deck and code samples will be
posted to my blog later tonight:
www.sqlsoldier.com