Backup & Restore

1. Why Backups?

Data loss can occur due to software bugs, human error (even by the DBA), system crashes, disk problems, disasters or theft. In short: anything.

Server backup ≠ database backup

Even if the server administrator already takes backups, separate database backups are necessary. Application errors and human error fall outside the scope of server backups.

2. Which Databases to Back Up?

System Databases

Database	When to back up
`master`	Regularly — for engine upgrades, after adding a new database
`msdb`	Regularly — contains job and alert history
`model`	After every change

Production Databases

Definitely after: - Adding a new database - Creating indexes - Running non-logged procedures

Definitely before: - Modifying the database structure - Deleting or clearing the transaction log

3. Physical vs Logical Backup

Type	Description	Advantages
Physical	Copies the underlying database files (.mdf, .ldf)	Faster, more compact
Logical	Writes SQL statements (CREATE, INSERT) to reconstruct the database	Compatible across DBMS versions, more flexible

4. Backup Types

Type	Contains	Use
Full	All data, structure + transaction log — state at the time of backup	Basis for all other backups — required starting point
Differential	All data changed relative to the last full backup	Faster than full, smaller file
Transaction log	History of changes (INSERT/UPDATE/DELETE) since the last log backup	Point-in-time recovery
Tail log	Last portion of the transaction log — before a restore	Prevents data loss right before a restore
Copy-only	A full backup without affecting the backup sequence	One-off extraction
File/filegroup	Individual file or filegroup	Large databases
Partial	Primary filegroup + read-write filegroups	Partial backup

5. Recovery Models

Model	Log backups	Data loss risk	Point-in-time recovery
simple	Not possible	Changes since the last backup	Only up to the end of the backup
full	Required	Normally none	Yes — to a specific point
bulk-logged	Required	Bulk operations possibly not covered	Up to the end of each backup

-- Set the recovery model
ALTER DATABASE ClassicModels SET RECOVERY FULL;
GO

6. Backup Schedule

A typical schedule combines multiple backup types:

Weekly:   Full backup          (10 GB base)
Daily:    Differential backup  (grows through the week)
Hourly:   Transaction log backup

Frequency: weekly | daily | hourly | continuous Retention: how long to keep it

7. Full Backup

Takes everything along: structure, data and the transaction log.

Via SSMSVia T-SQL

Object Explorer → right-click the database → Tasks → Back Up...

Set: Backup type = Full, Destination = Disk, file path.

BACKUP DATABASE [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH NOFORMAT, INIT;
GO

8. Differential Backup

Takes only the data changed relative to the last full backup.

Via SSMSVia T-SQL

Object Explorer → right-click the database → Tasks → Back Up...

Set: Backup type = Differential

BACKUP DATABASE [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH DIFFERENTIAL,
         NAME = N'ClassicModels-Full Database Backup';
GO

Note

Keep differential backups from growing too large — beyond a certain size there's no more speed benefit.

9. Transaction Log Backup

Only possible with the full or bulk-logged recovery model.

Keeps a history of all INSERT/UPDATE/DELETE not yet captured in an earlier log backup
Enables point-in-time recovery

Via SSMSVia T-SQL

Back Up... → Backup type = Transaction Log

Destination: a .trn file

BACKUP LOG [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.trn'
    WITH NAME = N'ClassicModels-Full Database Backup';
GO

10. Backup Devices

A backup device is a named alias for a physical backup file.

Via SSMSCreating via T-SQLUsing itRemoving

Object Explorer → Server Objects → right-click Backup Devices → New Backup Device...

Set: Device name, File path.

USE [master];
GO

EXEC sp_addumpdevice
    @devtype      = N'disk',
    @logicalname  = N'localDiskBackup',
    @physicalname = N'D:\Microsoft SQL Server\Backup\localDiskBackup.bak';
GO

USE [master];
GO

BACKUP DATABASE [ClassicModels]
    TO CM_Device;
GO

USE [master];
GO

EXEC sp_dropdevice @logicalname = 'localDiskBackup';
GO

11. Database Recovery

Automatic Recovery

Runs automatically when the SQL Server service (re)starts:

Every database's logical data state is checked based on the transaction log
Incomplete (uncommitted) transactions are rolled back
Completed but not-yet-written transactions are re-applied

Manual Recovery

Deliberately overwriting the database with data from a backup:

Always start from a full backup (overwrites physical files, recreates all tables)
Supplement with a differential backup (if present)
Supplement with transaction log backup(s)

12. Database Restore

Via SSMSVia T-SQL

Object Explorer → right-click the database → Tasks → Restore → Database...

Source: Database
Destination: Database + optionally a Timeline for point-in-time
Backup sets to restore: select which sets

USE [master];
GO

-- 1. Take a tail log backup (last log before the restore)
BACKUP LOG [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels_LogBackup_2021-09-18_11-02-25.trn'
    WITH NAME = N'ClassicModels_LogBackup_2021-09-18_11-02-25';

-- 2. Restore the full backup (NORECOVERY = not done yet)
RESTORE DATABASE [ClassicModels]
    FROM DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5;

-- 3. Restore the transaction log (NOUNLOAD = database stays online after restore)
RESTORE LOG [ClassicModels]
    FROM DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.trn'
    WITH FILE = 1, NOUNLOAD, STATS = 5;
GO

NORECOVERY vs RECOVERY

Use NORECOVERY as long as you still want to apply additional backups. Use RECOVERY (or omit it) on the last restore step — that's what brings the database online.