Backup & Recovery from First Principles, Part 10: Backup Encryption and TDE – The Gotchas That Cost You the Restore
Every part of this series so far assumed one thing: if you have the backup file, you can restore it. Encryption changes that deal. An encrypted backup (or any backup of a TDE database) is…
Backup & Recovery from First Principles, Part 9: BUFFERCOUNT, MAXTRANSFERSIZE, and Friends – Benchmarked
The BACKUP statement has a set of tuning knobs most people never touch: BUFFERCOUNT, MAXTRANSFERSIZE, BLOCKSIZE, and striping across multiple files.[1] Advice about them tends to be folklore, so this post is numbers instead: nine…
Backup & Recovery from First Principles, Part 8: Verification – Your Backup Job Succeeding Proves Almost Nothing
ended on an uncomfortable question: the “clean” full backup I used to repair a corrupted page – how did I know it was clean? This part demonstrates, with a deliberately corrupted database, exactly how little…
Backup & Recovery from First Principles, Part 7: Page-Level Restore – Fixing 8 KB Instead of 8 TB
Storage corrupted one 8 KB page in a large table. One option: restore the entire database from backup, losing hours to I/O. The better option, when the damage is small and isolated: restore just that…
Backup & Recovery from First Principles, Part 6: Piecemeal Restores – Online Before It’s All Back
A 4 TB database is down. The application needs the 200 GB of hot transactional data; the 3.8 TB of archived history can wait until tomorrow. A standard restore makes everyone wait for all 4…
Backup & Recovery from First Principles, Part 5: Restore Sequences and the STOPAT Rewind
The first four parts of this series were about taking backups. This one is about the operation they all exist for. A restore sequence is a small program you write under pressure, and its grammar…