SQL Server Administration Guide

Backup and Restore

This section covers essential strategies and procedures for backing up and restoring your SQL Server databases, ensuring data integrity and business continuity.

Why Backup and Restore?

Regular backups are critical for protecting your data against various threats, including:

• Hardware failures
• Software corruption
• Accidental data deletion or modification
• Malicious attacks (e.g., ransomware)
• Natural disasters

A well-defined restore strategy ensures you can quickly recover your databases to a consistent state with minimal data loss.

Backup Types

SQL Server supports several types of backups:

• Full Backup: Backs up the entire database. It is the foundation for all other backup types.
• Differential Backup: Backs up data that has changed since the last full backup. Restoring a differential backup requires the last full backup and the differential backup.
• Transaction Log Backup: Backs up the transaction log records. This is only available for databases in the FULL or BULK_LOGGED recovery models. Transaction log backups allow for point-in-time restores.

Backup Strategies

Choosing the right backup strategy depends on factors like database size, recovery point objective (RPO), and recovery time objective (RTO).

Example Strategy (Hybrid Approach)

• Daily: Full backup
• Hourly (during business hours): Transaction log backups (if applicable)
• Every 12 or 24 hours: Differential backup (optional, to speed up restores if full backups are large)

Performing Backups

Backups can be performed using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

Using SSMS:

1. In Object Explorer, right-click the database you want to back up.
2. Navigate to Tasks > Back Up....
3. Configure the backup type, destination, and options.

Using T-SQL:

Full Backup:

BACKUP DATABASE [YourDatabaseName]
TO DISK = N'C:\Backup\YourDatabaseName_Full.bak'
WITH NOFORMAT, NOINIT,  NAME = N'YourDatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Differential Backup:

BACKUP DATABASE [YourDatabaseName]
TO DISK = N'C:\Backup\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,  NAME = N'YourDatabaseName-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Transaction Log Backup:

BACKUP LOG [YourDatabaseName]
TO DISK = N'C:\Backup\YourDatabaseName_Log.trn'
WITH NOFORMAT, NOINIT,  NAME = N'YourDatabaseName-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Restoring Databases

Restoring a database involves recovering it from a backup file.

Using SSMS:

1. In Object Explorer, right-click the Databases node.
2. Select Restore Database....
3. Choose the backup source (Device or URL) and select your backup file.
4. Configure restore options, including recovery state.

Using T-SQL:

Restoring from a Full Backup (with NORECOVERY to allow further restores):

RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Full.bak'
WITH NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;
GO

Restoring from a Differential Backup (with NORECOVERY):

RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL, NORECOVERY, NOUNLOAD,  STATS = 5;
GO

Restoring from a Transaction Log Backup (with NORECOVERY):

RESTORE LOG [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Log.trn'
WITH NORECOVERY, NOUNLOAD,  STATS = 5;
GO

Bringing the Database Online (after all restores are complete):

RESTORE DATABASE [YourDatabaseName] WITH RECOVERY;
GO

Recovery Models

The recovery model of a database significantly impacts its backup and restore capabilities:

• SIMPLE: Minimal logging. Only full and bulk-copy backups are supported. Automatic truncation of the transaction log.
• FULL: Full transaction logging. Allows for point-in-time restores using transaction log backups. The transaction log must be manually backed up or truncated.
• BULK_LOGGED: A hybrid of SIMPLE and FULL. Most operations are minimally logged, but bulk operations (like BULK INSERT, SELECT INTO, index creation) are fully logged.

For production databases requiring point-in-time recovery, the FULL recovery model is typically recommended.

Best Practices

• Automate Backups: Use SQL Server Agent jobs to schedule regular backups.
• Backup Verification: Periodically run RESTORE VERIFYONLY to check backup integrity.
• Offsite Storage: Store backups in a secure, offsite location.
• Documentation: Maintain clear documentation of your backup schedule, storage locations, and restore procedures.
• Regular Testing: Conduct periodic restore tests to validate your backups and procedures.