SQL Server Differential Backup
“In SQL Server, a differential backup is a type of backup that captures the changes/modifications made to a database since the last full backup.
The differential backup scans the full backup, also known as the base of the differential backup, and compares it with the current database state. It then captures the changes and stores them.
A full backup can work as the base for various differential backups, including database backups, partial backups, and file backups.”
Advantages
The main advantage of differential backups is speed. In addition, since differential backup backups only the existing changes, it reduces the time and space required to back up the data in the server.
This can, in turn, help increase the frequency at which backups in a given SQL Server instance are issued.
Another advantage of differential backups is the number of log backups you need to restore.
SQL Server Create Differential Backup
There are two main methods you can use to create a differential backup in SQL Server:
- Using SQL Server Management Studio – Graphical
- Using T-SQL Commands
Let us illustrate how to use each of the methods above.
Sample Database
Let us create sample data to best capture the use and illustration of the differential backups.
Start switching to the master database:
Then, run the queries as provided:
create database resolver;
use resolver;
drop table if exists entries;
create table entries(
id int not null identity(1,
1) primary key,
server_name varchar(50),
server_address varchar(255) not null,
compression_method varchar(100) default 'none',
size_on_disk float not null,
size_compressed float,
total_records int not null,
init_date date
);
insert
into
ENTRIES(server_name,
server_address,
compression_method,
size_on_disk,
size_compressed,
total_records,
init_date)
values
('MySQL','localhost:3306','lz77',90.66,40.04,560000,'2022-01-02'),
('Redis','localhost:6307','Snappy',3.55,998.2,100000,'2022-03-19'),
('PostgreSQL','localhost:5432','pglz',101.2,98.01,340000 ,'2022-11-11'),
('Elasticsearch','localhost:9200','lz4',333.2,300.2,1200000,'2022-10-08');
This should create a new database and perform all the actions defined above.
SQL Server Full Backup
Before capturing a differential backup, we must first take a full database backup. For simplicity, we will use a T-SQL command to capture a full database backup.
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\resolver.copy'
WITH INIT,
NAME = 'Resolver DB Backup';
Make Changes to the Database
Once we have captured the full database backup, we need to make changes to the database to capture a differential backup.
We can do this by adding two more records to the entries table:
into
ENTRIES(server_name,
server_address,
compression_method,
size_on_disk,
size_compressed,
total_records,
init_date)
values
('MongoDB','localhost:27017','Snappy',4.55,4.10,620000,'2021-12-12'),
('Apache Cassandra','localhost:9042','zstd',300.3,200.12,10000000,'2020-03-21');
Once the changes are applied, we can run a differential backup.
SQL Server Differential Backup – SSMS
To take a differential backup of a given database in SSMS, expand the databases folder and right-click the target database.
Select Tasks –> Back Up.
In the Back Up Database Window, select the Backup Type as Differential.
You can add the target components such as files and filegroups, databases, etc. In our case, we need to take a differential database backup.
Finally, set the target file and click ok to process the backup.
Once complete, you should see a success message as:
SQL Server Differential Backup – T-SQL Command
To take a differential backup of a given database using Transact-SQL statements:
TO DISK = path_to_file
WITH DIFFERENTIAL;
Example usage:
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\resolver.diff'
WITH DIFFERENTIAL;
This should return the backup process logs as:
Processed 2 pages for database 'resolver', file 'resolver_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 106 pages in 0.016 seconds (51.513 MB/sec).
And there you have it.
Conclusion
This post aimed to introduce you to differential backups in SQL Server. You also learned how to capture full and differential backups using SSMS and T-SQL.
We hope we met your expectations.
Source: linuxhint.com