SQL Server Administration Guide: Backup and Restore

This section provides a comprehensive guide to backing up and restoring SQL Server databases. Proper backup and restore strategies are critical for data protection, disaster recovery, and maintaining business continuity.

Understanding Backup Types

SQL Server supports several types of backups, each serving a specific purpose:

Full Backups

A full backup captures the entire database, including data, transaction logs (up to the point of backup), and differential base information. It's the foundation for all restore operations.

• When to use: Regularly, as the primary backup strategy.
• Benefit: Simplest restore process.

Differential Backups

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

• When to use: Between full backups to speed up the backup process.
• Restore requirement: Requires the last full backup and the latest differential backup.

Transaction Log Backups

Transaction log backups capture all committed transactions that have not yet been backed up since the last log backup. This type of backup is only available for databases in the FULL or BULK_LOGGED recovery models.

• When to use: Frequently, to minimize data loss in case of a disaster. Essential for point-in-time restores.
• Restore requirement: Requires the last full backup, the last differential backup (if used), and a sequence of transaction log backups.

Performing Backups

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

Using SQL Server Management Studio (SSMS)

1. Connect to your SQL Server instance in SSMS.
2. Right-click on the database you want to back up.
3. Navigate to Tasks > Back Up....
4. Configure the backup type (Full, Differential, Transaction Log), destination, and other options as needed.
5. Click OK to start the backup.

Using Transact-SQL (T-SQL)

Full Backup Example:

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 Example:

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 Example:

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 data from backup files. The restore process depends on the type of backup performed and the desired recovery state.

Restoring a Full Backup

This is the most basic restore operation. You will need the full backup file.

Restoring with Differential and Log Backups

To restore to a specific point in time, you will need to apply backups in the correct order: the last full backup, followed by the last differential backup (if used), and then all subsequent transaction log backups up to the desired point.

Using SQL Server Management Studio (SSMS) for Restore

1. Connect to your SQL Server instance in SSMS.
2. Right-click on the Databases node and select Restore Database....
3. Under Source, select Device and browse to your backup file.
4. If restoring with differential or log backups, you may need to add subsequent backup files on the Files page under Restore Options.
5. Configure restore options, including the destination database name and recovery state (e.g., RESTORE WITH RECOVERY, RESTORE WITH NORECOVERY).
6. Click OK to start the restore.

Using Transact-SQL (T-SQL) for Restore

Restore Full Backup (and recover):

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

Restore Full, Differential, and Log Backups (to a point in time):

-- Restore the full backup, keeping the database not recovered
RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Full.bak'
WITH NORECOVERY, REPLACE, STATS = 10;
GO

-- Restore the differential backup, keeping the database not recovered
RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Diff.bak'
WITH NORECOVERY, STATS = 10;
GO

-- Restore transaction log backups up to a specific point in time
RESTORE LOG [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Log1.trn'
WITH NORECOVERY, STATS = 10;
GO

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

-- Recover the database to the specified point in time
RESTORE LOG [YourDatabaseName]
WITH STOPAT = 'YYYY-MM-DDTHH:MM:SS', RECOVERY;
GO

RESTORE VERIFYONLY FROM DISK = N'C:\Backup\YourDatabaseName_Full.bak';
GO

Backup Strategies and Best Practices

• Automate Backups: Use SQL Server Agent Jobs to schedule regular backups.
• Store Backups Offsite: Implement a strategy for storing backup copies in a geographically separate location.
• Monitor Backup Jobs: Ensure backup jobs are completing successfully.
• Document Your Plan: Maintain clear documentation of your backup and restore procedures.
• Regular Testing: Periodically perform test restores to validate your backup integrity and restore process.
• Retention Policies: Define and enforce policies for how long backups should be retained.

Implementing a robust backup and restore strategy is a cornerstone of effective SQL Server administration. By understanding the different backup types, performing backups regularly, and testing your restore process, you can significantly mitigate the risk of data loss and ensure the availability of your critical data.