Understanding SQL Server Database Backups and Restores

When working with SQL Server, it is crucial to understand the different types of backups and how to restore them. In this article, we will explore the differences between full, transaction log, and differential backups, and learn how to restore a database from each backup type.

Full Database Backup

A full backup is a complete backup of the entire database. It includes all the data and objects in the database and is typically stored in a *.bak file. A full backup can be restored by itself, without the need for any additional backups. It serves as the foundation for restoring transaction log and differential backups.

Transaction Log Backup

A transaction log backup captures all the changes made to the database since the last full backup or transaction log backup. It is typically stored in a *.trn file. Transaction log backups are used to restore the database to a specific point in time or to recover individual data pages. They are applied after a full backup has been restored.

Differential Backup

A differential backup captures only the data that has changed since the last full backup. It is typically stored in a *.dif file. Differential backups are used to reduce the time and storage required for restoring a database. They are applied after a full backup has been restored.

Why Do We Need Different Backup Types?

Having different backup types allows for more flexibility in managing database backups. For example, if you have a large database, taking a full backup every hour would be impractical due to the size of the backup files. Transaction log backups allow you to restore the database to a specific point in time, while differential backups capture only the changes since the last full backup, reducing the restore time.

Backup and Restore Examples

Let’s look at some examples of backing up and restoring a SQL Server database.

Example 1: Restoring from the Latest Full Backup

In this example, we will restore a database named MyDatabaseTest from the latest full backup of MyDatabase. The following T-SQL code can be executed in SQL Server Management Studio (SSMS) to perform the restore:

-- restore from the latest full backup
RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak' 
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',
MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',
RECOVERY, REPLACE;

This code restores the MyDatabaseTest database from the latest full backup file and makes it available for use.

Example 2: Restoring to the Latest Point in Time with Full and Transaction Log Backups

In this example, we will restore a database to the latest point in time using both full and transaction log backups. The following steps outline the restore process:

Restore the full backup:

RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak'
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',
MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',
NORECOVERY, REPLACE;

Restore the transaction log backups:

RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_001502_1580793.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_011501_4325128.trn' WITH NORECOVERY;
-- Repeat the above line for each transaction log backup file

Make the database available:

RESTORE LOG [MyDatabaseTest] WITH RECOVERY;

By restoring the full backup and applying the transaction log backups, the database is restored to the latest point in time.

Example 3: Restoring to an Earlier Point in Time

In this example, we will restore a database to an earlier point in time when a transaction log was backed up. The following steps outline the restore process:

Restore the full backup:

RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak'
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',
MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',
NORECOVERY, REPLACE;

Restore the differential backup:

RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_013001_9294408.dif' WITH NORECOVERY;

Restore the transaction log backups:

RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_031501_5822593.trn' WITH NORECOVERY;
-- Repeat the above line for each transaction log backup file

Make the database available:

RESTORE LOG [MyDatabaseTest] WITH RECOVERY;

By restoring the full backup, the differential backup, and the transaction log backups, the database is restored to the desired earlier point in time.

Example 4: Restoring to a Specific Point in Time Between Transaction Log Backups

In this example, we will restore a database to a specific point in time between transaction log backups. The following steps outline the restore process:

Restore the full backup:

RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak'
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',
MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',
NORECOVERY, REPLACE;

Restore the differential backup:

RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_013001_9294408.dif' WITH NORECOVERY;

Restore the transaction log backups:

RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn' WITH NORECOVERY,
STOPAT = '2021-06-01 02:30:00 AM';
-- Repeat the above line for each transaction log backup file

Make the database available:

RESTORE LOG [MyDatabaseTest] WITH RECOVERY;

By using the STOPAT option in the RESTORE LOG statement, the restore process stops at the specified point in time, allowing you to restore the database to a specific moment.

Understanding the different types of SQL Server database backups and how to restore them is essential for database administrators and developers. By mastering these concepts, you can ensure the integrity and availability of your data.

Article Last Updated: 2021-06-21

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on