SQL Server Documentation

Introduction to Backup and Restore

Database backup and restore operations are critical components of any data management strategy. They provide a mechanism to protect your data against loss due to hardware failures, software corruption, accidental deletions, or malicious attacks.

SQL Server offers robust features for creating backups and restoring databases to a consistent state. Understanding these features, the different types of backups, and the restore process is essential for database administrators.

Types of Backups

SQL Server supports several types of database backups, each serving a different purpose in your backup strategy.

Full Database Backup

A full database backup contains all the data and transaction log records necessary to recover the entire database. It represents a complete snapshot of the database at the time the backup was taken.

Full backups are the foundation of any backup strategy. All other backup types are based on a full backup.

Differential Database Backup

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

To restore a database using differential backups, you first need to restore the last full backup, followed by the last differential backup.

Transaction Log Backup

Transaction log backups capture the transaction log records that have been generated since the last log backup. These are only possible for databases in the Full or Bulk-Logged recovery models.

Transaction log backups are crucial for point-in-time recovery, allowing you to restore a database to a specific moment in time. They also help in truncating the transaction log, preventing it from growing indefinitely.

Performing a Backup

You can perform database backups 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. In Object Explorer, expand the Databases node.
3. Right-click on the database you want to back up.
4. Select Tasks > Back Up....
5. In the Back Up Database dialog box:
   • Choose the Backup type (Full, Differential, or Transaction Log).
   • Specify the Backup component (usually "Database").
   • In the Destination section, click Add... to specify the backup file location and name (e.g., C:\Backups\MyDatabase_Full.bak).
   • Configure other options such as backup set name, description, and expiration.
   • Click OK to start the backup.

Using Transact-SQL (T-SQL)

The general syntax for performing a backup using T-SQL is as follows:

BACKUP DATABASE [DatabaseName]
TO DISK = N'FilePath\FileName.bak'
WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

For different backup types, modify the WITH clause:

• Full Backup: The command above is for a full backup.
• Differential Backup:

  BACKUP DATABASE [DatabaseName]
  TO DISK = N'FilePath\FileName_Diff.bak'
  WITH DIFFERENTIAL, NOFORMAT, NOINIT,  NAME = N'DatabaseName-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
  GO
  

• Transaction Log Backup:

  BACKUP LOG [DatabaseName]
  TO DISK = N'FilePath\FileName_Log.trn'
  WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
  GO
  

Performing a Restore

Restoring a database is the process of retrieving it from a backup. This is a critical operation that can be performed using SSMS or T-SQL.

Restore Options

When restoring a database, you'll typically need to consider:

• Database Name: The name of the database to restore.
• Source Files: The backup file(s) to use.
• Restore Location: Where the restored database files (.mdf, .ldf) will be placed.
• Recovery State: Whether to leave the database ready for use (WITH RECOVERY), ready for subsequent restores (WITH NORECOVERY), or ready for rollback (WITH ROLLBACK ONLY). WITH NORECOVERY is commonly used when applying multiple backups (e.g., full, differential, and log).

Using SSMS for Restore

1. Connect to your SQL Server instance in SSMS.
2. Right-click on the Databases node and select Restore Database....
3. In the Restore Database dialog box:
   • Under Source, select Device and click the browse button (...) to select your backup file.
   • Under Destination, specify the Database name. If restoring to the same server, you can choose an existing database (which will be overwritten) or provide a new name.
   • Go to the Files page to review and adjust the logical and physical file names for the restored database if necessary.
   • Go to the Options page to select restore options, including the Recovery state. For applying multiple backups, select RESTORE WITH NORECOVERY.
   • Click OK to start the restore process.
4. If applying multiple backups, repeat steps 1-6 for each subsequent backup file, ensuring the Recovery state is set appropriately for each step. Finally, perform the last restore with RESTORE WITH RECOVERY.

Using T-SQL for Restore

The general syntax for restoring a database using T-SQL:

RESTORE DATABASE [DatabaseName]
FROM DISK = N'FilePath\FileName.bak'
WITH FILE = 1,  -- Use the first backup set in the file
MOVE N'LogicalDataFileName' TO N'PhysicalDataFilePath.mdf',
MOVE N'LogicalLogFileName' TO N'PhysicalLogFilePath.ldf',
NOUNLOAD,  REPLACE, STATS = 5
GO

Important Notes for T-SQL Restore:

• Replace [DatabaseName] with your desired database name.
• Replace FilePath\FileName.bak with the path to your backup file.
• You must specify the correct MOVE options for both data and log files, mapping the logical names from the backup to their desired physical locations. You can find logical names using RESTORE FILELISTONLY FROM DISK = 'FilePath\FileName.bak'.
• Use REPLACE if restoring over an existing database.
• For applying multiple backups sequentially (e.g., full + differential + log), use NORECOVERY in all intermediate restores and RECOVERY in the final restore.

-- Example: Restoring a Full backup WITH NORECOVERY
RESTORE DATABASE [DatabaseName]
FROM DISK = N'C:\Backups\MyDatabase_Full.bak'
WITH MOVE N'MyDatabase_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQLXX\MSSQL\DATA\MyDatabase.mdf',
MOVE N'MyDatabase_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQLXX\MSSQL\DATA\MyDatabase_log.ldf',
NORECOVERY, REPLACE, STATS = 10
GO

-- Example: Restoring a Differential backup WITH NORECOVERY
RESTORE DATABASE [DatabaseName]
FROM DISK = N'C:\Backups\MyDatabase_Diff.bak'
WITH MOVE N'MyDatabase_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQLXX\MSSQL\DATA\MyDatabase.mdf',
MOVE N'MyDatabase_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQLXX\MSSQL\DATA\MyDatabase_log.ldf',
NORECOVERY, REPLACE, STATS = 10
GO

-- Example: Restoring a Transaction Log backup WITH RECOVERY (last in sequence)
RESTORE LOG [DatabaseName]
FROM DISK = N'C:\Backups\MyDatabase_Log.trn'
WITH RECOVERY, STATS = 10
GO

Understanding Recovery Models

The recovery model of a database determines how transaction log records are managed, which directly impacts backup and restore capabilities, especially point-in-time recovery.

Full Recovery Model

In the Full recovery model, all transactions are logged. This allows for complete recovery of the database, including point-in-time recovery. Transaction log backups are essential to manage log space and enable recovery.

Use Case: Critical production databases where minimizing data loss is paramount.

Bulk-Logged Recovery Model

The Bulk-Logged recovery model is a hybrid. It logs most operations fully but logs certain bulk operations (like bulk inserts, `SELECT INTO`, `ALTER INDEX`) minimally. This can improve performance for bulk operations.

Point-in-time recovery is possible, but the transaction log backups might not contain all the detailed information for every transaction if bulk operations occurred.

Use Case: Databases with frequent large bulk operations where performance is a concern, but point-in-time recovery is still needed.

Simple Recovery Model

In the Simple recovery model, transaction logs are automatically truncated (cleared) after transactions are committed and data is written to the data files. This means only full and differential backups can be performed. Point-in-time recovery is not possible.

Use Case: Development or test databases, or databases where data loss is acceptable and point-in-time recovery is not a requirement.

-- For Full Recovery Model
ALTER DATABASE [DatabaseName] SET RECOVERY FULL;

-- For Simple Recovery Model
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE;

Best Practices for Backup and Restore

• Regularly Test Backups: The most crucial step. Periodically restore your backups to a separate environment to ensure they are valid and complete.
• Follow a Backup Strategy: Define a schedule that balances data protection needs with storage and performance considerations (e.g., daily full, hourly differential, frequent log backups).
• Store Backups Offsite: Keep copies of your backups in a different physical location than your production servers to protect against site-wide disasters.
• Use Descriptive File Names: Include database name, backup type, and timestamp in your backup file names for easy identification.
• Monitor Backup Jobs: Ensure your scheduled backup jobs are running successfully and investigate any failures promptly.
• Document Your Strategy: Keep clear documentation of your backup schedules, retention policies, and restore procedures.
• Consider Backup Compression: SQL Server Enterprise Edition and later versions support backup compression, which can save storage space and potentially reduce backup time.
• Verify Backups: Use the RESTORE VERIFYONLY command to check the integrity of a backup file without actually restoring it.

Troubleshooting Common Issues

• Backup Fails with "Disk is full": Ensure there is enough free space on the destination drive for the backup file.
• Restore Fails with "The log or differential backup cannot be restored because the database is not in the correct state": This usually indicates an issue with the recovery state (e.g., trying to restore with RECOVERY when further log backups need to be applied) or an incorrect sequence of backups.
• Cannot Find Backup File: Verify the path and filename are correct. Ensure the SQL Server service account has read permissions to the backup location.
• Corrupt Backup File: Try using RESTORE VERIFYONLY on other backup files to see if the corruption is isolated or widespread. If a backup file is corrupt, you may need to resort to the last known good backup.
• Permissions Issues: Ensure the SQL Server service account has necessary permissions (e.g., read/write) on the backup directories.