How do you manage backups and recovery in SQL?

Backup and recovery are the foundation of any database administrator’s responsibility, and even as a developer or data engineer, understanding them deeply is vital to ensure business continuity.

When I’m asked this in an interview, I usually start by explaining that my goal in managing backups and recovery is to achieve minimal data loss (RPO) and minimal downtime (RTO) based on the organization’s recovery objectives. The approach I use depends on the database size, criticality, and infrastructure setup (on-premise or cloud).

So, I’ll explain it step by step —

1. Types of Backups I Use:
In SQL Server (and similar RDBMS systems), I generally work with three main types of backups:

• Full Backup:
  This takes a complete snapshot of the entire database — data, schema, indexes, and transaction logs.
  Example:

BACKUP DATABASE SalesDB  
TO DISK = 'D:\Backups\SalesDB_Full.bak'  
WITH INIT, COMPRESSION;

I typically schedule this once a day or weekly, depending on the data change rate.

Differential Backup:
This captures only the data that has changed since the last full backup — faster and smaller in size.

BACKUP DATABASE SalesDB  
TO DISK = 'D:\Backups\SalesDB_Diff.bak'  
WITH DIFFERENTIAL;

I usually run this every few hours to shorten recovery time.

Transaction Log Backup:
This backs up all transactions since the last log backup and helps achieve point-in-time recovery.

BACKUP LOG SalesDB  
TO DISK = 'D:\Backups\SalesDB_Log.trn';

• I schedule this every 15 or 30 minutes for critical databases.

Together, these backups form a recovery chain, which lets me restore data to any exact point in time.

2. Automation and Retention Strategy:
I never rely on manual backups — I use SQL Agent jobs, PowerShell scripts, or cloud-based schedulers (like Azure Automation or AWS Lambda) to automate the entire backup process.

For example, in one enterprise setup, I configured a backup retention policy:

• Daily full backups (kept for 7 days)
• Hourly differential backups (kept for 48 hours)
• 15-minute transaction log backups (kept for 24 hours)

Older backups were automatically deleted or moved to cold storage (like Azure Blob Archive tier) to save cost.

3. Verifying Backup Integrity:
A backup is useless unless it’s restorable. I always include verification steps like:

RESTORE VERIFYONLY FROM DISK = 'D:\Backups\SalesDB_Full.bak';

And, at least once a week, I perform test restores to a staging environment. This ensures the backups are valid and recovery steps are well-documented.

4. Recovery Process (Real Example):
Once, in a production system, a user accidentally deleted records from a financial transactions table. Instead of restoring the entire database, I used point-in-time recovery:

1. Restored the last full backup to a temporary database.
2. Applied all transaction log backups up to the minute before deletion.
3. Extracted the lost data from the temporary database and merged it back into production.

That incident reinforced the importance of frequent transaction log backups and tested recovery plans.

5. Challenges I’ve Faced:

• Storage constraints: Backups can consume a lot of space. I solved this by enabling backup compression and storing old backups on cheaper cloud storage.
• Long restore times: For very large databases, full restore could take hours. I implemented filegroup backups to restore critical portions first.
• Backup failures: I configured alerts (via SQL Agent or email) so that any backup job failure is immediately notified.

6. Disaster Recovery and High Availability Integration:
In distributed or high-availability systems, I combine backups with replication or mirroring for extra protection. For example:

• In SQL Server Always On Availability Groups, each replica can perform its own backups, distributing load.
• In Azure SQL, I rely on automated geo-redundant backups that support point-in-time recovery for up to 35 days.

7. Alternatives and Enhancements:

• Snapshots: For faster recovery in virtualized or cloud environments.
• Log shipping: Maintains a standby database for near real-time failover.
• Database cloning or differential restores: For large-scale test environments.

In summary:
My backup and recovery strategy always revolves around three pillars — reliability, automation, and verification.
I take full, differential, and log backups based on RPO/RTO needs, automate them, verify their integrity regularly, and document the recovery steps thoroughly.