- Capability
- SQL Server backup and recovery
- Gap
- No complete backup capability after deployment
- Decision
- Use a native, DBA-operable T-SQL solution
- Outcome
- Tested GFS protection restored on the correct backup target
A SQL Server platform had been deployed and its replication tested for disaster recovery. The platform could survive the failure scenarios covered by the replication design, but one important gap remained: there was no complete backup and recovery capability.
Two external options were available. The first was a third-party snapshot or agent-based product. It could provide backups, but it would introduce vendor dependency and leave SQL DBAs restoring databases through a tool they did not routinely use. The second was to pass the requirement to an external managed service provider, adding another operational boundary around a responsibility the database team needed to understand and own.
Neither option made recovery simpler. A native solution was therefore designed using complete T-SQL backup routines. It used capabilities already understood by SQL Server DBAs, wrote backups to the intended separate target and implemented the required grandfather-father-son retention model. The routines and recovery process were tested successfully.
A new DBA later joined the organisation and decided to replace the native routines with an SSIS-based process. SSIS is capable of orchestrating database backups; the technology itself was not the problem. The problem was that the replacement was introduced without first understanding the requirements already embodied in the working solution.
The revised process did not preserve the established backup routines, retention model, compliance obligations or target design. Backups were redirected to local disk. They still appeared to be running, but they now consumed capacity on the SQL platform itself, shared its failure domain and no longer satisfied the original protection and retention requirements.
Local disk consumption became an operational risk, and the organisation lost the compliance position the original design had provided. A change presented as an alternative implementation had changed the outcome as well as the tooling.
The appropriate response was to restore the tested native T-SQL solution. It was simpler, familiar to the people responsible for recovery and already proven against the actual requirement.
No engineer knows everything, and a new approach should always be open to consideration. But authority to change a platform is not a substitute for understanding why its controls exist. A working solution should only be replaced when the alternative has been shown to preserve or improve every material requirement, including recovery, retention, compliance, capacity and operational ownership.
Principia evidence
Principia is Praetorian's private, AI-supported operational source of truth. It brings governed evidence, system relationships, deterministic findings and assisted interpretation together to provide traceable operational insight.
Principia flags when a database has no full backup within the expected period, links the warning to the affected system and retains the measurements behind it. That starts a proper investigation; retention, target separation and restore testing still need to be checked before anyone can claim the service is recoverable.
Engineering lessons
- A successful backup job is not evidence of a complete recovery capability.
- Replacement designs must preserve retention, compliance, target separation and restore operability—not merely produce backup files.
- Native tooling can be the strongest operational choice when it keeps recovery understandable and under the DBA team's control.
- Empowerment should enable challenge and improvement, not bypass the need to understand a working design.
Read the engineering principles behind this work →
Confidentiality: Engineering Notes are based on real engagements. Client identities, timelines and identifying details may be changed to protect confidentiality. The engineering decisions and lessons remain representative of the work undertaken.