Backup Types

1. Full

2. Differential

3. Transaction log

Full backup:

A full backup backs up the full/whole database. That backs up all the data.

The full database backup has been done in all the recovery models.

Differential backup:

A differential backup only backs up the changed data/extents that were modified after the full database backup has done. The DCM has tracked all the changed extents.

The differential backup has been done in all the recovery models.

Transaction log backup:

A transaction log backup backs up the transaction log file. That is all the modifications/changes.

The transaction log backup only possible in full and bulk logged recovery model.

The transaction log backup is important to minimize data loss and log file size too.

It’s very important to keep the transaction log backup as much as safe, because the restoration needs the sequence of the transaction log backup if, you deleted/missing any one of the backups then you can’t recover the whole data.

It’s good to take a log backup often. It will reduce the log file size and helps to minimize your data loss.

Recovery Models

SQL server has three types of recovery models.

1. Full

2. Bulk Logged

3. Simple

Full:

In full recovery model all the database operations are fully logged like all redo information. This means all the modifications have written fully to the log file.

The full recovery model is always the best one for production servers.

In the full recovery model we can take all kinds of backups like full, differential, transaction Log and point in time recovery (PTR) too.

Bulk Logged:

The name itself you can understand the answer. The bulk operations (bulk operations) are minimally logged in this mode. This means it does not have sufficient information in the log to replay the transaction. The BCM page contains all the required information. See an example you can understand.

Bulk logged recovery model is the best model for server performance. Because all the bulk changes have been written minimally (Not fully) to the transaction log file.

In bulk logged recovery model we can take all kinds of backups like full, differential and transaction Log but, the drawback is the point in time recovery (PTR) is not possible, when there is a bulk operations have done with the transaction log file.

Keep in mind, in full and bulk logged recovery model the log files grow bigger until the BACKUP LOG has done.

Note: The bulk logged model is good only for the bulk operations.

Simple:

The name itself you can understand the answer all operations are fully logged except bulk operation, Since bulk are minimally logged. Simple recovery model is just simple this means SQL server will run the checkpoint every time and truncate the transaction log file and marked the space for reuse . Mostly the log file will not grow larger.

Most of the time the simple recovery model is a good choice for non production servers. Because the log file will not grow larger. Also we would not take log backups. (If you’re planning to take the log backup then don’t put in simple)

In the simple recovery model the transaction log backup is not possible. We can take full and differential backups only.

INNER JOIN WITH EXAMPLES

create table Emp(

id int identity(1,1) primary key,
Username varchar(50),
FirstName varchar(50),
LastName varchar(50),
DepartID int

)

insert into emp values (‘nirampatel’,’niram’,’patel’,1)
insert into emp values (‘rajivpat’,’rajiv’,’pat’,1)
insert into emp values (‘madhurpani’,’madhur’,’pani’,2)
insert into emp values (‘gouravkhan’,’gourav’,’khan’,2)
insert into emp values (‘akshaykumar’,’akshay’,’kumar’,3)
insert into emp values (‘sardarpandit’,’sardar’,’pandit’,null)

create table Depart(

id int identity(1,1) primary key,
DepartmentName varchar(50)

)

insert into depart values (‘it’),(‘cse’),(‘eee’),(‘ece’),(‘mech’),(‘civil’)

inner join:

select e1.Username,e1.FirstName,e1.LastName,e2.DepartmentName
from Emp e1 inner join Depart e2 on e1.DepartID=e2.id

join:
SELECT * FROM Emp e1 JOIN Depart e2 ON e1.DepartID = e2.id

left outer join:
SELECT * FROM Emp e1 LEFT OUTER JOIN Depart e2
ON e1.DepartID = e2.id

right outer join:
SELECT * FROM Emp e1 RIGHT OUTER JOIN Depart e2
ON e1.DepartID = e2.id

full outer join:
SELECT * FROM Emp e1 FULL OUTER JOIN Depart e2
ON e1.DepartID = e2.id

cross join:
SELECT * FROM Emp cross join Depart e2

Get Index Fragmentation Percentage For All Tables In SQL Server Database

select object_name(itable.object_id) as tablename,
itable.name as IndexName,
indexfrag.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(), null, null, null, ‘DETAILED’) indexfrag
inner join sys.indexes itable on itable.object_id = indexfrag.object_id
and itable.index_id = indexfrag.index_id
— make sure to set this where clause to the percentage below which you want to exclude results.
where indexfrag.avg_fragmentation_in_percent > 20
order by avg_fragmentation_in_percent desc, tablename