How to Restore a Differential Backup in SQL Server?

How 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:

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:

 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).