SQL Server Database Engine: Backup and Restore

Backup and Restore Operations for SQL Server Database Engine

This section provides comprehensive guidance on backup and restore strategies for the SQL Server Database Engine. Effective backup and restore procedures are critical for data protection, disaster recovery, and maintaining the integrity of your databases.

Overview

SQL Server offers robust mechanisms for backing up and restoring databases. Understanding the different backup types, strategies, and restore options is essential for any SQL Server administrator.

Backup Types

Full Backups: Backs up the entire database. This is the foundation of any backup strategy.
Differential Backups: Backs up only the data that has changed since the last full backup. This reduces backup time and storage space compared to full backups.
Transaction Log Backups: Backs up the transaction log. These are crucial for point-in-time recovery in databases using the FULL or BULK_LOGGED recovery models.

Backup Strategies

Developing a sound backup strategy involves considering factors such as:

Recovery Point Objective (RPO): The maximum acceptable amount of data loss.
Recovery Time Objective (RTO): The maximum acceptable time to restore operations after an outage.
Database size and activity level.
Available storage and network bandwidth.

Common strategies include:

Regular full backups.
Periodic differential backups between full backups.
Frequent transaction log backups (especially for high-transaction databases).

Restoring Databases

Restoring a database involves applying one or more backup files to recover the database to a specific point in time.

Restoring a Full Backup: The most basic restore operation.
Restoring Differential Backups: Requires the last full backup followed by the latest differential backup.
Restoring Transaction Log Backups: Requires the last full backup, followed by any differential backups, and then all subsequent transaction log backups in sequence.
Point-in-Time Restore: Restores the database to a specific point in time using transaction log backups.

Key Concepts and Considerations

Recovery Models: SIMPLE, FULL, and BULK_LOGGED affect what types of backups can be performed and the granularity of restores.
Backup Devices: Backups can be written to disk, tape, or a URL (for Azure SQL Database and SQL Server on Azure VMs).
Backup Verification: It is highly recommended to verify backups using the RESTORE VERIFYONLY command.
Native Backups vs. Copy-Only Backups: Copy-only backups do not affect the backup chain, useful for creating ad-hoc backups without disrupting the regular backup schedule.

Syntax Examples

Full Backup

BACKUP DATABASE AdventureWorks2019
TO DISK = 'D:\Backups\AdventureWorks2019_Full.bak'
WITH NOINIT, NOUNLOAD, NAME = 'AdventureWorks2019 Full Backup', STATS = 10;

Differential Backup

BACKUP DATABASE AdventureWorks2019
TO DISK = 'D:\Backups\AdventureWorks2019_Diff.bak'
WITH DIFFERENTIAL, NOINIT, NOUNLOAD, NAME = 'AdventureWorks2019 Differential Backup', STATS = 10;

Transaction Log Backup

BACKUP LOG AdventureWorks2019
TO DISK = 'D:\Backups\AdventureWorks2019_Log.trn'
WITH NOINIT, NOUNLOAD, NAME = 'AdventureWorks2019 Log Backup', STATS = 10;

Restore Full Backup

RESTORE DATABASE AdventureWorks2019
FROM DISK = 'D:\Backups\AdventureWorks2019_Full.bak'
WITH NORECOVERY, REPLACE;

Restore Transaction Log Backup

RESTORE LOG AdventureWorks2019
FROM DISK = 'D:\Backups\AdventureWorks2019_Log.trn'
WITH NORECOVERY;

Finalize Restore (bring database online)

RESTORE DATABASE AdventureWorks2019 WITH RECOVERY;

MSDN Documentation