What Is a Transaction Log?

Transaction log is a list of records for all Microsoft SQL database changes. It is an essential component of the SQL Server database. If there is a system failure, you will be able to use the T-log to bring your database back to a consistent state before the point of failure.

Transaction log records tend to grow over time and may fill up the entire disk, which can bring the whole database to the halt.

If you run a database in full or bulk-logged recovery model, then transaction logs must be backed up. Transaction log truncation happens after each successful backup automatically.

How to Create T-log Backups Using SQL Server Tools

Right-click on the desired database and select the Tasks – Back Up option. Change the Backup Type to Transaction Log and you’re ready to go.

2. Then you can simply convert these backup settings to the scheduled job by clicking Script at the top:

3. Most of the settings are self-explanatory, so let’s check the Scheduling tab:

Full Database Backups.

A full database backup backs up the whole database. This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished.

Differential.sql

Differential Backups

Differential only back changes since the last full backup
to fully understand the procsess of Differential Backups
i have create new database called BackupDatabase

then i will check the database revoery model usung the below qurey

select name,recovery_model_desc
from sys.databases order by name 

now we will create table

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2)
);

insert values

INSERT INTO Product (ProductID, ProductName, Price)
VALUES
    (1, 'Product A', 10.99),
    (2, 'Product B', 19.99),
    (3, 'Product C', 8.49),
    (4, 'Product D', 14.95),
    (5, 'Product E', 5.99),
    (6, 'Product F', 12.50),
    (7, 'Product G', 9.75),
    (8, 'Product H', 7.99),
    (9, 'Product I', 11.25),
    (10, 'Product J', 18.50);

now we will take full database backup

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backup\YourDatabaseName.bak'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD;

now insert 10 more random value

take transaction log file

BACKUP LOG BackupDatabase
TO DISK = 'E:\DBBACKUP\database-backup-full.bak';

take look inside the bak file

restore headeronly from disk = 'E:\DBBACKUP\database-backup-full.bak'

the qurey will show data inside bak file

type 1 is fullbackup
type 2 is log backup

now insert more values

now take direnetial backup

here is sytix

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backup\YourDatabaseName_DifferentialBackup.bak'
WITH DIFFERENTIAL;

BACKUP DATABASE BackupDatabase
TO DISK = 'E:\DBBACKUP\database-backup-full.bak'
WITH DIFFERENTIAL;

the qurey we used before show that back type is 5 which is for Differential backup

now i will restore to difrintial back point

databse is restored to orginal point

Please note that these steps are general guidelines, and it’s important to have proper backups of your database files before making any changes. Additionally, the specific steps may vary slightly depending on the version of SQL Server you are using or the database management tool you prefer.