6. DATABASE BACKUP

Question

6. DATABASE BACKUP

Gurdeep Vaidya 0

Tell me all I need to know about how to backup a database and types of backups

Trevor Waldenburg 0

Backup

Why backup: hardware failure, human error (DELETE without WHERE), ransomware, software bugs.

3 Recovery models:

Model	Log	Max data loss	Use for
SIMPLE	Auto-truncated	Since last backup	Development
--------	--------	--------	--------
SIMPLE	Auto-truncated	Since last backup	Development
FULL	Preserved until backed up	Near zero	Production
BULK-LOGGED	Minimal for bulk ops	Bulk operations lost	During bulk import

sql

ALTER DATABASE Northwind SET RECOVERY FULL
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'Northwind'

4 Backup types:

Type	What	When
Full	Entire database	Weekly
--------	--------	--------
Full	Entire database	Weekly
Differential	Changes since last full	Daily
Log	Transaction log	Hourly
Tail-log	Last transactions before restore	Before failover

sql

BACKUP DATABASE Northwind TO DISK = 'C:\backup\NW_full.bak'
    WITH FORMAT, NAME = 'Full Backup', COMPRESSION

BACKUP DATABASE Northwind TO DISK = 'C:\backup\NW_diff.bak'
    WITH DIFFERENTIAL

BACKUP LOG Northwind TO DISK = 'C:\backup\NW_log.bak'

-- Tail-log (closes database):
BACKUP LOG Northwind TO DISK = 'C:\backup\NW_tail.bak'
    WITH NORECOVERY

-- Copy-only (doesn't break backup sequence):
BACKUP DATABASE Northwind TO DISK = 'C:\backup\NW_copy.bak'
    WITH COPY_ONLY

-- Backup history:
SELECT database_name, backup_start_date, type, backup_size/1024/1024 AS MB
FROM msdb.dbo.backupset WHERE database_name = 'Northwind'
ORDER BY backup_finish_date DESC

1 answer

Your answer

Trevor Waldenburg 0 Reputation points

2026-05-16T01:53:03.9233333+00:00

Backup

Why backup: hardware failure, human error (DELETE without WHERE), ransomware, software bugs.

3 Recovery models:

Model Log Max data loss Use for

SIMPLE Auto-truncated Since last backup Development

-------- -------- -------- --------

SIMPLE Auto-truncated Since last backup Development

FULL Preserved until backed up Near zero Production

BULK-LOGGED Minimal for bulk ops Bulk operations lost During bulk import

sql

ALTER DATABASE Northwind SET RECOVERY FULL SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'Northwind'

4 Backup types:

Type What When

Full Entire database Weekly

-------- -------- --------

Full Entire database Weekly

Differential Changes since last full Daily

Log Transaction log Hourly

Tail-log Last transactions before restore Before failover

sql

BACKUP DATABASE Northwind TO DISK = 'C:\backup\NW_full.bak' WITH FORMAT, NAME = 'Full Backup', COMPRESSION BACKUP DATABASE Northwind TO DISK = 'C:\backup\NW_diff.bak' WITH DIFFERENTIAL BACKUP LOG Northwind TO DISK = 'C:\backup\NW_log.bak' -- Tail-log (closes database): BACKUP LOG Northwind TO DISK = 'C:\backup\NW_tail.bak' WITH NORECOVERY -- Copy-only (doesn't break backup sequence): BACKUP DATABASE Northwind TO DISK = 'C:\backup\NW_copy.bak' WITH COPY_ONLY -- Backup history: SELECT database_name, backup_start_date, type, backup_size/1024/1024 AS MB FROM msdb.dbo.backupset WHERE database_name = 'Northwind' ORDER BY backup_finish_date DESC

Answer 1

In SQL Server, backing up a database means creating a copy of its data and/or transaction log so it can be restored and recovered after a failure or to another location.

A backup (noun) is the copy; to back up (verb) is the process of creating that copy by copying data records from a database or log records from its transaction log.

At a high level, the backup/restore process works as follows:

Backups are written to backup devices (disk/tape/Azure Blob) as backup media (one or more files or tapes).
A restore operation copies data and log pages from the backup back into a database and then rolls forward logged transactions to bring the database to a transaction-consistent state (recovery).
The database recovery model (SIMPLE, FULL, BULK_LOGGED) controls how the transaction log is maintained and what backup types are allowed.

Main backup types in SQL Server

Full database backup

A full backup is a data backup that contains all the data in a specific database or set of filegroups or files, plus enough transaction log to recover that data.
A full database backup represents the whole database as it existed when the backup finished.
A full backup is required before differential or transaction log backups can be taken.

Differential backup

A differential backup is a data backup based on the latest full backup of the database, partial database, or set of files/filegroups (the differential base).
It contains only the data extents that have changed since that base.
Differential backups are smaller and faster than full backups but depend on the last full backup. To restore, restore the full backup and then the last differential backup.

Transaction log backup

A log backup contains all transaction log records that were not backed up in a previous log backup (in the full recovery model).
Log backups are used to recover a database to the point of failure or to a specific point in time.
Transaction log backups are not available in the SIMPLE recovery model.

File and filegroup backups

A file backup is a backup of one or more database files or filegroups.
These are data backups that allow backing up subsets of a database, which can be useful for very large databases.
Under the SIMPLE recovery model, file and filegroup backups are available only for read-only filegroups.

Partial backup

A partial backup contains data from only some of the filegroups in a database:
- Always includes the primary filegroup and every read/write filegroup.
- Can optionally include read-only filegroups.
A differential partial backup records only the data extents that have changed in those filegroups since the previous partial backup.

Copy-only backup

A copy-only backup is a special-use backup that is independent of the regular backup sequence.
It does not affect the differential base or the log backup chain.
Useful when an ad-hoc backup is needed (for example, before a one-off operation) without disturbing scheduled full/differential/log backups.
Copy-only can be applied to full backups and log backups; it cannot be combined with the Differential option.

Data backup (umbrella term)

A data backup refers to backups of data in:
- A complete database (database backup),
- A partial database (partial backup), or
- A set of data files or filegroups (file backup).

How to perform a full database backup with SQL Server Management Studio (SSMS)

Connect to the SQL Server Database Engine in SSMS and open Object Explorer.
Expand the server, then expand Databases and select the user or system database to back up.
Right-click the database → Tasks → Back Up…
In the Back Up Database dialog:
- Confirm the Database selection.
- In Backup type, choose Full (default).
- Under Backup component, select Database.
In the Destination section:
- Review or change the backup file location.
- Use Back up to to select a device (disk, etc.).
- Use Add to add backup files; multiple files can be used (striping) for performance.
Optionally adjust Media Options and Backup Options pages (for example, overwrite vs append, compression, etc.).
Select OK to start the backup, then OK again when it completes.

Key configuration points in the SSMS Back Up Database dialog

Database: choose which database to back up.
Recovery model: view-only; shows SIMPLE, FULL, or BULK_LOGGED for the selected database.
Backup type options:
- Full (for databases, files, filegroups).
- Differential (for databases, files, filegroups).
- Transaction Log (for transaction logs; not available in SIMPLE recovery model).
Copy Only Backup: check this to create a copy-only backup that does not affect the backup chain (not allowed when Differential is selected).
Backup component:
- Database: backs up the entire database.
- Files and filegroups: backs up selected files/filegroups.

Glossary of core terms

Back up: process of creating a backup from a database or its transaction log.
Backup: the resulting copy of data/log used for restore and recovery.
Backup device: disk or tape device (or Azure Blob via URL) where backups are written.
Backup media: the actual files or tapes that contain one or more backups.
Database backup: backup of a database (full or differential).
Differential backup: backup of only data changed since the last full backup.
Full backup: backup of all data in the database/files/filegroups plus enough log to recover it.
Log backup: backup of transaction log records not yet backed up.
Recover: return a database to a stable, consistent state.
Recovery: phase that brings the database into a transaction-consistent state during startup or restore.
Recovery model: database property (SIMPLE, FULL, BULK_LOGGED) that determines log maintenance and backup/restore requirements.
Restore: process of copying data/log pages from backups to a database and applying logged changes.

Example use of copy-only backups in operations

Before an in-place upgrade or risky change, a copy-only full backup can be taken so it does not interfere with existing differential backup chains.
For example, when preparing for an upgrade, a copy-only backup can be created in addition to regular backups to have an extra restore point without changing the backup sequence.

References:

Share via

6. DATABASE BACKUP

Backup

1 answer

Your answer