SQL Server Backup & Restore Strategies

Ensuring data recovery is the most critical task for any SQL Server DBA. This guide covers the essential T-SQL commands for performing full, differential, and transaction log backups.

1. Full Database Backup

A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

BACKUP DATABASE [SalesDB]
TO DISK = 'C:\Backups\SalesDB_Full.bak'
WITH FORMAT,
MEDIANAME = 'SalesDB_Backup',
NAME = 'Full Backup of SalesDB';
GO

2. Transaction Log Backup

Log backups allow you to restore the database to a specific point in time. They are only available in Full or Bulk-Logged recovery models.

BACKUP LOG [SalesDB]
TO DISK = 'C:\Backups\SalesDB_Log.trn'
WITH NOINIT,
NAME = 'SalesDB Log Backup';
GO

3. Restoring a Database

When restoring, always start with the Full backup. Use WITH NORECOVERY if you plan to apply log backups afterwards.

-- Restore Full Backup
RESTORE DATABASE [SalesDB]
FROM DISK = 'C:\Backups\SalesDB_Full.bak'
WITH NORECOVERY;

-- Restore Log Backup
RESTORE LOG [SalesDB]
FROM DISK = 'C:\Backups\SalesDB_Log.trn'
WITH RECOVERY;
GO

Summary

Always verify your backups with RESTORE VERIFYONLY to ensure they are valid and usable when disaster strikes.