Skip to main content
Our Tech Ideas

SQL Server Backup and Restore: A Complete Guide

SQL Server Backup and Restore: A Complete Guide

Data is the backbone of any business, and ensuring its safety is crucial. Since SQL Server provides a comprehensive set of backup and restore options, safeguarding your data becomes more manageable. In this guide, we will walk through the different recovery models, backup types, restore methods, and best practices to ensure efficient database management.

1. SQL Server Recovery Models

SQL Server employs different recovery models to control logging and data recovery options:

  • Simple: Minimal logging, no log backups, quick recovery.
  • Full: Complete logging, supports point-in-time recovery.
  • Bulk-Logged: Minimal logging for bulk operations, but limited point-in-time recovery.

If you need to change the recovery model, use the following command:

ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL; -- Or SIMPLE/BULK_LOGGED
GO

For an in-depth guide on SQL Server Recovery Models, visit Microsoft’s official documentation.

2. SQL Server Backup Types

Backup Types

  1. Full Backup: Captures the entire database, forming the foundation for all subsequent backups.
  2. Differential Backup: Backs up changes since the last full backup.
  3. Transaction Log Backup: Captures transaction logs for point-in-time recovery.
  4. Copy-Only Backup: Independent backup that does not affect the backup chain.
  5. File/Filegroup Backup: Backs up specific files or filegroups.

For more details on each type of backup, check out this SQL Backup Guide.

3. Verification During Backup

Ensuring backup integrity is vital. To verify your backup file, use the following command:

RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\YourDatabaseName.bak';
GO

For additional steps on verifying backups, refer to SQL Server Backup Verification.

4. Restore Types and Procedures

Restore Options

  • WITH RECOVERY: Makes the database operational.
  • WITH NORECOVERY: Keeps the database in restoring mode for further backups.
  • WITH STANDBY: Allows the database to be readable during the restore process.

Restoring a Full Backup

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

For a complete step-by-step restoration guide, visit Microsoft SQL Restore Documentation.

5. Restore with Mirroring

Database mirroring ensures high availability by maintaining a mirrored copy on a secondary server.

Steps for Mirroring Restore:

  1. First, take a full backup on the primary server:
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backups\YourDatabaseName_Full.bak'
WITH FORMAT, STATS = 10;
GO
  1. Next, restore the full backup on the secondary server:
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak'
WITH NORECOVERY, STATS = 10;
GO
  1. Then, take and restore transaction log backups:
BACKUP LOG [YourDatabaseName]
TO DISK = 'C:\Backups\YourDatabaseName_Log.trn';
GO

RESTORE LOG [YourDatabaseName]
FROM DISK = 'C:\Backups\YourDatabaseName_Log.trn'
WITH NORECOVERY, STATS = 10;
GO
  1. Finally, configure mirroring via SQL Server Management Studio (SSMS) or T-SQL.

For a deeper understanding of database mirroring, check out this Mirroring Guide.