SQL Server Maintenance Tasks and resore bakup files - Asp.Net,C#,Sql server,jquery,Angularjs,Mvc,interview tips and coding tips

These are the SQL Server Maintenance Plan tasks available in the wizard. Each task serves a different purpose for database health, performance, and backup strategy.

Task	Purpose	When to Use
Check Database Integrity	Runs `DBCC CHECKDB` to verify database consistency and detect corruption.	Weekly or daily for critical databases.
Shrink Database	Reduces the physical size of data/log files by removing unused space.	Only when a database has grown abnormally and won't need the space again. Not recommended as a regular task.
Reorganize Index	Defragments indexes online with minimal locking.	When index fragmentation is between 5% and 30%.
Rebuild Index	Drops and recreates indexes to remove fragmentation completely.	When fragmentation exceeds 30%. Improves query performance but uses more resources.
Update Statistics	Refreshes statistics used by the query optimizer.	Daily or weekly depending on data changes.
Clean Up History	Removes old SQL Server Agent, backup, and maintenance history records.	Monthly. Prevents MSDB from growing too large.
Execute SQL Server Agent Job	Runs an existing SQL Agent job.	When you want to include custom jobs within the maintenance plan.
Back Up Database (Full)	Creates a complete backup of the database.	Daily or weekly depending on recovery requirements.
Back Up Database (Differential)	Backs up only changes since the last Full Backup.	Daily between Full backups to reduce backup size and time.
Back Up Database (Transaction Log)	Backs up transaction logs. Enables point-in-time recovery.	Every 15–60 minutes for databases using Full Recovery Model.
Maintenance Cleanup Task	Deletes old backup files, reports, and maintenance files.	To prevent backup folders from filling the disk.

Backup Types Explained

1. Full Backup

Contains the entire database.

Example:


Sunday: Full Backup

If the database crashes Monday morning, restore the Sunday Full Backup.

2. Differential Backup

Contains changes since the last Full Backup.

Example:


Sunday  : Full Backup
Monday  : Differential
Tuesday : Differential
Wednesday : Differential

To restore Wednesday:


1. Restore Sunday Full Backup
2. Restore Wednesday Differential Backup

No need to restore Monday and Tuesday differentials.

3. Transaction Log Backup

Contains all transactions since the previous log backup.

Example:


09:00 AM Full Backup
10:00 AM Log Backup
11:00 AM Log Backup
12:00 PM Log Backup

If failure occurs at 12:15 PM:


1. Restore Full Backup
2. Restore 10:00 Log
3. Restore 11:00 Log
4. Restore 12:00 Log
5. Recover to 12:15 PM

This provides the lowest possible data loss.

Recommended Maintenance Plan for Most Production Databases

Daily

✅ Check Database Integrity

✅ Full Backup (Night)

✅ Maintenance Cleanup Task

Weekly

✅ Rebuild/Reorganize Index

✅ Update Statistics

If Database Uses FULL Recovery Model

✅ Full Backup (Night)

✅ Differential Backup (Every 4–6 hours)

✅ Transaction Log Backup (Every 15–30 minutes)

Example:


12:00 AM  Full Backup
06:00 AM  Differential
12:00 PM  Differential
06:00 PM  Differential
Every 15 minutes Transaction Log Backup

This gives very fast recovery with minimal data loss.

Tasks Generally NOT Recommended as Scheduled Jobs

❌ Shrink Database

Reasons:

Causes index fragmentation
Slows performance
Database often grows again immediately

Only run manually when:

Large data/archive deletion occurred
You permanently need less disk space

For your maintenance plan screenshot, if this is a normal production database, I would typically select:

✅ Check Database Integrity
✅ Rebuild Index (or Reorganize Index)
✅ Update Statistics
✅ Back Up Database (Full)
✅ Back Up Database (Differential)
✅ Maintenance Cleanup Task

And add Transaction Log Backup only if the database recovery model is FULL.

then how restore the differencial backup with full backp

To restore a Differential Backup, you must first restore the Full Backup on which the differential is based.

Example Backup Schedule


01-Jun 12:00 AM  Full Backup
02-Jun 12:00 AM  Differential Backup
03-Jun 12:00 AM  Differential Backup
04-Jun 12:00 AM  Differential Backup

Suppose the database crashes on 04-Jun at 10:00 AM.

You only need:


1. Full Backup (01-Jun)
2. Latest Differential Backup (04-Jun)

You do NOT restore the 02-Jun and 03-Jun differential backups.

Using SSMS GUI

Step 1: Restore Full Backup

Right-click Databases
Restore Database
Device → Select Full Backup (.bak)
In Options:
- Check Restore WITH NORECOVERY

This leaves the database in a restoring state.

Step 2: Restore Differential Backup

Restore Database again
Select Differential Backup file
In Options:
- Check Restore WITH RECOVERY

This brings the database online.

Using T-SQL

Restore Full Backup


RESTORE DATABASE MyDB
FROM DISK = 'F:\Backup\MyDB_Full.bak'
WITH NORECOVERY;

Restore Differential Backup


RESTORE DATABASE MyDB
FROM DISK = 'F:\Backup\MyDB_Diff.bak'
WITH RECOVERY;

If You Also Have Transaction Log Backups

Example:


01-Jun 12:00 AM Full Backup
04-Jun 06:00 AM Differential Backup
04-Jun 07:00 AM Log Backup
04-Jun 08:00 AM Log Backup
04-Jun 09:00 AM Log Backup
Crash at 09:30 AM

Restore sequence:


-- Full
RESTORE DATABASE MyDB
FROM DISK='Full.bak'
WITH NORECOVERY;

-- Differential
RESTORE DATABASE MyDB
FROM DISK='Diff.bak'
WITH NORECOVERY;

-- Log backups
RESTORE LOG MyDB
FROM DISK='Log_0700.trn'
WITH NORECOVERY;

RESTORE LOG MyDB
FROM DISK='Log_0800.trn'
WITH NORECOVERY;

RESTORE LOG MyDB
FROM DISK='Log_0900.trn'
WITH RECOVERY;

Restore order is always:


Full Backup
    ↓
Latest Differential Backup
    ↓
All Log Backups after that Differential
    ↓
Recovery

Important

A Differential Backup is tied to the most recent Full Backup that existed when it was created. If you lose the Full Backup file, the Differential Backup alone cannot be restored.