MSDN Documentation

1. Full Backups

A full backup is the foundation of your backup strategy. It contains all the data and transaction log information needed to restore the database to the point in time the backup was taken.

When to use:

• As the initial backup before any other backup type.
• Regularly, depending on your recovery point objectives (RPO).

SQL Command Example:

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName_Full.bak'
WITH COMPRESSION, STATS = 10;

2. Differential Backups

Differential backups back up only the data that has changed since the last full backup. This reduces backup time and storage space compared to full backups.

When to use:

• Between full backups to speed up the backup process.
• Requires a preceding full backup.

SQL Command Example:

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

3. Transaction Log Backups

Transaction log backups capture the transaction log records generated since the last log backup. These are essential for point-in-time recovery and are only available for databases in the Full or Bulk-Logged recovery model.

When to use:

• For point-in-time recovery capabilities.
• Regularly in conjunction with full and differential backups.

SQL Command Example:

BACKUP LOG YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName_Log.trn'
WITH COMPRESSION, STATS = 10;

4. Restoring a Full Backup

This is the most basic restore operation, bringing the database back to the state of the full backup.

SQL Command Example:

RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak'
WITH REPLACE, RECOVERY;

REPLACE overwrites an existing database. RECOVERY allows users to access the data immediately. Use NORECOVERY if you plan to apply further differential or log backups.

5. Restoring with Differential and Log Backups

To achieve point-in-time recovery, you must restore the full backup first (with NORECOVERY), followed by the latest differential backup (if applicable, with NORECOVERY), and then all subsequent transaction log backups in order, with the final log backup restored using RECOVERY.

SQL Command Example (Sequence):

-- Restore Full Backup
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak'
WITH NORECOVERY;

-- Restore Latest Differential Backup
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Diff.bak'
WITH NORECOVERY;

-- Restore Transaction Log Backups (in order)
RESTORE LOG YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Log1.trn'
WITH NORECOVERY;

RESTORE LOG YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Log2.trn'
WITH RECOVERY; -- The last log restore brings the database online

Always restore transaction logs in the exact order they were created. Missing a log file or restoring them out of order will prevent successful recovery.

6. Restoring to a Specific Point in Time

This advanced technique allows you to restore the database to a precise moment before an incident occurred, using transaction log backups.

SQL Command Example:

RESTORE LOG YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Log.trn'
WITH STOPAT = '2023-10-27 10:30:00', RECOVERY;

Ensure your server's time zone settings are consistent when specifying the STOPAT time.