Full, Differential, Transaction Log Backups and Restore - Textnotes

Full, Differential, Transaction Log Backups and Restore

Learn SQL backup and recovery in this comprehensive guide. Master full, differential, and transaction log backups. Restore databases and perform point-in-time recovery. Practice database backup and restore to ensure data safety and reliability.

1. Introduction

Database backup and recovery is critical to protect against data loss due to hardware failure, accidental deletion, or corruption.

  1. Backups allow restoring data to a specific point in time.
  2. Recovery strategies ensure minimal downtime and data integrity.

Key Points:

  1. Always maintain regular backups.
  2. Test backups to ensure they can be restored successfully.
  3. Understand the differences between full, differential, and transaction log backups.

2. Types of Backups

2.1 Full Backup

  1. Captures the entire database at a point in time.
  2. Basis for all other backups.

-- SQL Server example
BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDatabase_Full.bak';

2.2 Differential Backup

  1. Captures only changes since the last full backup.
  2. Smaller and faster than full backups.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDatabase_Diff.bak'
WITH DIFFERENTIAL;

2.3 Transaction Log Backup

  1. Captures all changes in the transaction log since the last log backup.
  2. Allows point-in-time recovery.

BACKUP LOG YourDatabase
TO DISK = 'C:\Backups\YourDatabase_Log.trn';

3. Restore Database

3.1 Restore Full Backup


RESTORE DATABASE YourDatabase_Restore
FROM DISK = 'C:\Backups\YourDatabase_Full.bak'
WITH MOVE 'YourDatabase_Data' TO 'C:\Data\YourDatabase_Restore.mdf',
MOVE 'YourDatabase_Log' TO 'C:\Data\YourDatabase_Restore.ldf';

3.2 Restore Differential Backup


RESTORE DATABASE YourDatabase_Restore
FROM DISK = 'C:\Backups\YourDatabase_Full.bak'
WITH NORECOVERY;

RESTORE DATABASE YourDatabase_Restore
FROM DISK = 'C:\Backups\YourDatabase_Diff.bak'
WITH RECOVERY;

3.3 Restore Transaction Log for Point-in-Time


RESTORE DATABASE YourDatabase_Restore
FROM DISK = 'C:\Backups\YourDatabase_Full.bak'
WITH NORECOVERY;

RESTORE LOG YourDatabase_Restore
FROM DISK = 'C:\Backups\YourDatabase_Log.trn'
WITH STOPAT = '2025-12-05 10:00:00', RECOVERY;

4. Practical Exercises

  1. Take a full backup of your database.
  2. Make some changes and take a differential backup.
  3. Take a transaction log backup after multiple inserts/updates.
  4. Restore the database from full backup to a new database.
  5. Restore using differential and transaction log backups for point-in-time recovery.

5. Tips for Beginners

  1. Always store backups on a separate storage from the database.
  2. Schedule regular full, differential, and transaction log backups.
  3. Test backup and restore procedures frequently.
  4. Use NORECOVERY for intermediate restores and RECOVERY for final restore.
  5. Keep transaction log backups frequent for minimal data loss.


Next Step: After mastering backup and recovery, the next module is Advanced Optimization in SQL, where you’ll learn query tuning, index maintenance, execution plans, table partitioning, and materialized views.