SQL Server Database Engine - Backup and Restore

Introduction to Backup and Restore

SQL Server's backup and restore functionality is a fundamental aspect of database management. It allows you to create point-in-time copies of your databases and to recover them in case of hardware failure, software corruption, human error, or other catastrophic events.

Key concepts include:

Backup Types: Understanding the differences between Full, Differential, and Transaction Log backups.
Recovery Models: How Simple, Full, and Bulk-Logged recovery models affect backup and restore capabilities.
Backup Media: Storing backups on disk, tape, or cloud storage.
Restore Scenarios: Recovering databases to their original location, a new location, or a specific point in time.

Backup Strategies

Developing a robust backup strategy tailored to your business needs is paramount. Consider factors such as RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

Full Backups

A full backup captures all the data and transaction log records necessary to restore the database to the state it was in when the backup completed. It is the foundation of any backup strategy.

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

Differential Backups

A differential backup captures only the data that has changed since the last full backup. This reduces backup time and storage space compared to frequent full backups.

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

Transaction Log Backups

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

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

Note: Ensure your database is set to the appropriate recovery model (Full or Bulk-Logged) to enable transaction log backups.

Restore Operations

Restoring a database involves applying one or more backup files to recreate the database.

Restoring a Full Backup

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

                        RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak' WITH REPLACE, STATS = 10;
                    

Restoring a Full Backup with Differential

To restore using a full backup and a subsequent differential backup, you first restore the full backup and then the differential backup. The database will be restored to the state of the differential backup.

                        RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak' WITH REPLACE, NORECOVERY, STATS = 10;
                        RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabaseName_Diff.bak' WITH RECOVERY, STATS = 10;
                    

Point-in-Time Restore

To perform a point-in-time restore, you must have a sequence of Full, Differential (optional), and Transaction Log backups. You restore the full and differential backups with NORECOVERY, and then apply transaction log backups sequentially up to the desired point in time.

                        -- Restore Full and Differential (if used) with NORECOVERY
                        RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak' WITH REPLACE, NORECOVERY, STATS = 10;
                        RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabaseName_Diff.bak' WITH NORECOVERY, STATS = 10;

                        -- Restore Transaction Logs up to the desired point
                        RESTORE LOG [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabaseName_Log1.trn' WITH NORECOVERY, STATS = 10;
                        RESTORE LOG [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabaseName_Log2.trn' WITH STOPAT = '2023-10-27 10:30:00', RECOVERY, STATS = 10;
                    

The STOPAT clause specifies the exact date and time up to which the transaction log is applied. The final log backup should be restored with RECOVERY.

Conceptual diagram illustrating backup and restore flow.

Best Practices

Regularly Test Backups: Periodically perform test restores to ensure your backups are valid and can be used for recovery.
Store Backups Offsite: Keep copies of your backups in a separate physical location to protect against site-wide disasters.
Document Your Strategy: Clearly document your backup schedule, retention policies, and restore procedures.
Monitor Backup Jobs: Implement monitoring to alert you if backup jobs fail.
Secure Backup Files: Protect your backup files from unauthorized access or deletion.

Backup and Restore Operations