Point-in-time recovery restores a database to a selected moment within a sequence of transaction-log backups. The required backup files form a restore chain, so recovery planning must account for both acceptable data loss and downtime. section 12.12 section 12.12
A common mistake is switching to bulk-logged recovery during an ETL run and forgetting to switch back. Bulk-logged recovery reduces logging, but it does not support recovery to a point in the middle of a transaction-log backup; restoration stops at that backup’s end. section 9.4
MarketingArchive during ETL
MarketingArchive can use bulk-logged recovery temporarily to reduce log usage and I/O during bulk ETL operations. At the end of the run, return to FULL recovery and take a transaction-log backup; otherwise the restore strategy loses point-in-time precision. section 9.4
Use these safeguards when designing or operating the recovery process:
- Use FULL recovery when the required recovery point objective demands transaction-log backups, particularly for production OLTP databases. section 12.12
- Define RPO and RTO before choosing the backup strategy: RPO limits data loss, while RTO limits downtime. section 12.12
- Validate backups with RESTORE WITH VERIFY ONLY, and avoid taking backups during ETL windows when processes cannot be rerun. section 12.12
- After changing a database to FULL recovery, perform a full backup before relying on the resulting backup chain. section 12.12
Listing 9.10 Setting the database recovery model to bulk logged
ALTER DATABASE MarketingArchive SET RECOVERY BULK_LOGGED WITH NO_WAIT ;
Inspect the T-SQL command used to place MarketingArchive in bulk-logged recovery, a temporary optimization that requires a later return to FULL recovery for point-in-time protection. section 9.4
