Step-by-Step Guide to Backup & Restore (2000–2025

1️⃣ Pre-requisites
✔ SQL Server installed
✔ Database in Full Recovery Model (for log backups)
✔ Sufficient disk space for backup storage

2️⃣ Performing a Full Database Backup

📌 Using T-SQL

BACKUP DATABASE MyDatabase
TO DISK = 'C:\SQLBackups\MyDatabase_Full.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10;

📌 Using SSMS

1️⃣ Right-click database → Tasks → Back Up
2️⃣ Choose Full Backup
3️⃣ Select destination path
4️⃣ Click OK to start the backup
3️⃣ Performing a Differential Backup

BACKUP DATABASE MyDatabase
TO DISK = 'C:\SQLBackups\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

4️⃣ Performing a Transaction Log Backup

BACKUP LOG MyDatabase
TO DISK = 'C:\SQLBackups\MyDatabase_Log.trn'
WITH COMPRESSION, NO_TRUNCATE;

5️⃣ Restoring a Database from Backup

📌 Full Restore

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\SQLBackups\MyDatabase_Full.bak'
WITH NORECOVERY, REPLACE;

📌 Apply Differential Backup

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\SQLBackups\MyDatabase_Diff.bak'
WITH NORECOVERY;

📌 Apply Transaction Log Backup

RESTORE LOG MyDatabase
FROM DISK = 'C:\SQLBackups\MyDatabase_Log.trn'
WITH RECOVERY;

📌 Automating Backups Using PowerShell

# Define Variables
$SQLServer = "MySQLServer"
$Database = "MyDatabase"
$BackupPath = "C:\SQLBackups\MyDatabase.bak"

# Perform Full Backup
Invoke-Sqlcmd -ServerInstance $SQLServer -Query "BACKUP DATABASE [$Database] TO DISK = '$BackupPath' WITH COMPRESSION;"

Write-Host "✅ Full Backup Completed for $Database"

📌 Best Practices for SQL Server Backup & Restore

1️⃣ Use Backup Compression (Reduces storage and speeds up backups)
2️⃣ Store Backups Offsite (For disaster recovery readiness)
3️⃣ Automate Backup Schedules (SQL Server Agent Jobs or PowerShell)
4️⃣ Monitor Backup Integrity (Use CHECKSUM option & restore test backups regularly)
5️⃣ Use Tail-Log Backups for Critical Systems (Prevents data loss during crashes)