Database Recovery with Piecemeal Restore in Microsoft SQL Server
July 28, 2026| 7 min read
How to Restore a Differential Backup in SQL Server?
Written By Bharat Bhushan in
Approved By Raja Jegan R in
Updated on 5 Feb 2024
Min Reading 7 Min
Our content follows trusted Editorial Standards - accurate & unbiased.
Summary: A differential backup in SQL Server captures only the changes done after the full backup. In this article, we will show you how to back up and restore a differential backup in SQL Server. In addition, we will mention a Stellar Repair for MS SQL Technician that can help recover differential backup if it gets corrupted. A differential backup is more efficient as it stores only the changes made since the last full backup. Such backups are smaller in size because they do not store the entire database. Let’s learn how to create differential backups in SQL Server and how to restore them.
Free Download 100% SecureHow to Create a Differential Database Backup in SQL Server?
You can create a differential backup in SQL Server using SSMS or T-SQL.
Create Differential Backup using SSMS
In SQL Server Management Studio (SSMS), right-click the database and select Tasks > Back Up.
The differential backup requires a full backup first. Chose the Full Backup type.
Then, add a new table with data to do some changes in the database.
--Create a table named email
CREATE TABLE email (
id INT,
email VARCHAR(50));
--insert data into the table email.
INSERT INTO email (id, email)
VALUES (1, 'john.doe@example.com'),
(2, 'jane.doe@example.com'),
(3, 'james.smith@example.com'),
(4, 'mary.johnson@example.com'),
(5, 'robert.brown@example.com');
Now, right-click the database and select Tasks > Back Up in the SSMS Object Explorer.
This time, choose the Differential backup type.
Create Differential Backup using T-SQL Code
You can also create a differential backup using T-SQL code. This way you can automate the tasks.
First, use the following code to do a full backup.
BACKUP DATABASE [stellar] TO DISK = N'C:\data\DIFF.bak'
WITH NOFORMAT, NOINIT,
NAME = N'stellar-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
This will back up the database named ‘stellar’ in a file named ‘diff.bak’.
Before proceeding, let’s understand the different options used in the code.
- NOFORMAT means that the backup is not formatted.
- NOINIT is used to indicate that the backup operation should not overwrite the existing media header.
- NAME is used to add a name for the backup set.
- SKIP is used to skip any media header records during the backup process.
- NOREWIND is used to specify that the tape should not be rewound after the backup process.
- NOUNLOAD is used to specify that the tape should not be unloaded after the backup operation is finished.
- STATS is used to display the progress during the backup. Number 10 means that the backup progress will initiate in 10 and continue every 10 percent.
Once the full backup is done, you can create the differential backup. Use the following code:
BACKUP DATABASE [stellar] TO DISK = N'C:\data\DIFF.bak'
WITH DIFFERENTIAL ,
NOFORMAT, NOINIT, NAME = N'stellar-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
How to Restore Differential Backup in SQL Server?
To restore a differential backup, you can use SSMS or T-SQL code.
Select the Device option and press the Browse button.
Press the Add button and select the .bak file.
Note: In the above example, we stored the backup at the C:\data\DIFF.bak path. However, you can choose any path of your preference.
Once the backup file is selected, press OK.
Now, you will see the Full and Differential backups in the Backup sets to restore. Press OK.
Restore Differential Backup using T-SQL
First, check if your backup file is valid using the below code:
RESTORE VERIFYONLY FROM DISK = 'C:\data\diff.bak'
If it is fine, you can go ahead and restore it.
Use the following code to restore your database:
USE [master]
RESTORE DATABASE [stellar] FROM DISK = N'C:\data\diff.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE DATABASE [stellar] FROM DISK = N'C:\ data\diff.bak ' WITH FILE = 2, NOUNLOAD, STATS = 5
GO
First, you need to restore the full backup (FILE 1). Then, restore the differential backup (FILE 2).
In above example, the full backup is in NORECOVERY mode, which means that it is not available because it is waiting for the FILE 2 to restore.
Also, you can use the NOUNLOAD option and the STAT option.
- STATS is used to display the progress during the restoration. Number 5 means that the backup progress will initiate in 5 and continue every 5 percent.
- NOUNLOAD instructs SQL Server to not unload the tape from the drive upon completion of the backup.
What to do if the differential backup is damaged or corrupt?
Your differential backup may get damaged due to hardware problems, natural disasters, or viruses. If that is the case, you can use a third-party software, such as Stellar Repair for MS SQL Technician to restore your damaged backup.
You can download the software from the official page and install it. Once installed, select the Extract from MS SQL Backup module.
Select the backup file to scan.
Also, select the Standard Scan mode. This is faster than Advanced Scan.
In addition, select the backup to recover and press Next.
If everything is fine, you will receive a message that the backup is successfully repaired.
Now, press the Save button.
You can save the backup in a New Database, Live Database (existing database online), or in other formats (Excel, HTML, or CSV). We will create a new database. Press Next.
Write the SQL Server name and choose the authentication method.
Select the Fast-Saving method.
Now, go to SSMS to check the repaired dataset (Recovered_Stellar).
Conclusion
In this article, we learned how to do a differential backup in SQL Server using SSMS and T-SQL. We also learned how to restore differential backups using SSMS and T-SQL. We have also mentioned Stellar Repair for MS SQL Technician – an advanced SQL repair tool that can come in handy if your differential backup gets damaged or corrupt.
About The Author
Bharat Bhushan in
Senior Content Writer
This article was written by Bharat Bhushan, a Content Writer at Stellar Data Recovery.
Curious about our content creation process? Take a look at our Editor Guidelines.
7 min read
