7 questions foundWhat are the three main types of backups in SQL Server, and what does each one capture?
Beginner A full backup captures the entire database at a specific point in time, a differential backup captures only the changes made since the last full backup, and a transaction log backup captures all the transaction activity since the previous log backup, letting you restore your database to a very precise point in time.
BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase_Full.bak';
BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase_Diff.bak' WITH DIFFERENTIAL;
BACKUP LOG MyDatabase TO DISK = 'C:\Backups\MyDatabase_Log.trn';
Real-world example A retail company takes a full backup every Sunday night, differential backups every weeknight, and transaction log backups every fifteen minutes, giving them the ability to restore their database to almost any specific moment if something goes wrong.
Common follow-ups: Why would a company use differential backups instead of just taking full backups more often?;What recovery model does a database need to support transaction log backups?
Transactions & ACID;SQL Server Architecture & Editions
How do you restore a SQL Server database from a full backup file?
Beginner You use the RESTORE DATABASE statement, specifying the database name and the location of the backup file, and SQL Server will recreate the database exactly as it existed at the moment that full backup was taken, which is the starting point for any more complex restore involving additional differential or log backups.
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH RECOVERY;
Real-world example An administrator restores a company's customer database from last night's full backup after an accidental deletion, quickly bringing the important customer data back online for the team.
Common follow-ups: What is the difference between WITH RECOVERY and WITH NORECOVERY during a restore?;Can you restore a backup to a server with a different name than the original?
SQL Server Architecture & Editions;Transactions & ACID
What are the three recovery models available in SQL Server, and how do they affect your backup strategy?
Intermediate The simple recovery model automatically clears the transaction log after each checkpoint and does not support log backups, the full recovery model keeps a complete log of every transaction, allowing point in time recovery, and the bulk logged model is similar to full but minimally logs certain large bulk operations to improve performance during those specific operations.
ALTER DATABASE MyDatabase SET RECOVERY FULL;
Real-world example A hospital's patient records database uses the full recovery model so that if a failure occurs, the database can be restored to the exact moment before the failure, rather than only to the last full or differential backup.
Common follow-ups: Which recovery model is the right default choice for most production databases?;What happens to your ability to recover data if you switch from full to simple recovery model?
Transactions & ACID;Data Types & Schema Design
How would you perform a point in time restore to recover a database to the exact moment just before an accidental data deletion?
Intermediate You restore the most recent full backup with the NORECOVERY option, then restore any differential backup if one exists, and finally restore the transaction log backups in order, using the STOPAT option on the final log restore to specify the exact date and time just before the accidental deletion occurred.
RESTORE DATABASE MyDatabase FROM DISK = 'Full.bak' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log1.trn' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log2.trn' WITH STOPAT = '2026-09-06 14:30:00', RECOVERY;
Real-world example A finance team accidentally deletes an important table of transactions at 2:35pm, and the database administrator restores the database to 2:30pm using a point in time restore, recovering all the lost data with minimal impact.
Common follow-ups: What happens if you specify a STOPAT time that is after the last available transaction log backup?;Why is the order of restoring full, differential, and log backups important?
Transactions & ACID;Isolation & Locking
How would you design a comprehensive backup and recovery strategy for a large, business critical database with strict recovery time and recovery point objectives?
Advanced You would schedule full backups on a regular cycle such as weekly, differential backups daily to reduce restore time, and frequent transaction log backups, perhaps every five to fifteen minutes, to minimize potential data loss, while also regularly testing full restores on a separate server to verify your backups actually work and meet your required recovery time.
-- Example schedule
-- Full backup: Sunday 1am
-- Differential backup: daily at 1am
-- Transaction log backup: every 15 minutes
Real-world example An online payment processor designs a backup strategy with frequent log backups every five minutes to meet a strict recovery point objective, and regularly practices full restores in a test environment to confirm they can recover quickly during a real emergency.
Common follow-ups: What is the difference between recovery time objective and recovery point objective?;How often should backup restores actually be tested in a real environment?
Always On Availability Groups;Transactions & ACID
How do you verify that a backup file is not corrupted and can actually be restored successfully?
Advanced You use the RESTORE VERIFYONLY command to check that a backup file is structurally complete and readable without actually performing a full restore, though the most reliable way to confirm a backup truly works is to periodically perform an actual test restore onto a separate, non production server.
RESTORE VERIFYONLY FROM DISK = 'C:\Backups\MyDatabase_Full.bak';
Real-world example A company's database administrator runs RESTORE VERIFYONLY on every backup as an automated nightly check, catching a corrupted backup file early before it becomes the only available copy during an actual emergency.
Common follow-ups: Does RESTORE VERIFYONLY guarantee the backup will restore successfully every time?;How often should full test restores be performed in addition to verification checks?
Backup & Recovery;SQL Server Agent & Job Scheduling
How do you automate regular backups in SQL Server so they run consistently without manual effort?
Intermediate You create a SQL Server Agent job with a defined schedule, such as running a full backup weekly and transaction log backups every fifteen minutes, and configure the job to alert an administrator by email if any backup step fails, ensuring your backup strategy runs reliably without someone needing to remember to do it manually.
-- SQL Server Agent job step example
BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backups\MyDatabase_' + CONVERT(varchar, GETDATE(), 112) + '.bak';
Real-world example A small company sets up a SQL Server Agent job to automatically back up their database every night and email an alert to the IT team if the job ever fails, avoiding the risk of forgetting a manual backup.
Common follow-ups: How do you set up email alerts for a failed SQL Server Agent job?;Should backup files be stored on the same server as the database itself?
SQL Server Agent & Job Scheduling;Always On Availability Groups