Why does RESTORE DATABASE need ALTER DATABASE ... SET SINGLE_USER first?

SQL Server won't overwrite a database file while other connections might be using it — SINGLE_USER WITH ROLLBACK IMMEDIATE forcibly disconnects everyone else and rolls back their open transactions, guaranteeing the restore has exclusive access. Skipping this step on a database with active connections makes RESTORE fail rather than risk corrupting an in-use file.

What does WITH REPLACE actually do on a RESTORE?

It permits overwriting an existing database with the same name — without it, SQL Server refuses to restore over a database that already exists, as a safety check against accidentally clobbering the wrong target. This lesson's disaster recovery scenario intentionally overwrites the (now missing-a-table) bookshop database, so WITH REPLACE is required.

Does BACKUP DATABASE capture data that was written after the backup started?

It captures a transactionally consistent snapshot as of when the backup completes, including changes made to unrelated parts of the database while the backup itself was running — but nothing written after the backup finishes. This lesson's DROP TABLE happened after the backup completed, which is exactly why restoring from that backup brings the table back.

How can I confirm a backup actually happened, without re-running it?

msdb.dbo.backupset records every backup SQL Server has taken against a database — querying it by database_name shows real start/finish times and the actual backup size, which is the reliable way to audit backup history instead of trusting that a scheduled job ran without checking.

Is a full BACKUP DATABASE enough for real production disaster recovery?

It's the foundation, but production systems typically layer differential and transaction log backups on top of periodic full backups, to shrink both backup time and the data-loss window between the last backup and a failure. A full backup alone means losing everything written since that backup if disaster strikes right before the next one.

A Real Disaster and a Real Recovery: BACKUP, DROP TABLE, RESTORE

Backing up a database and actually needing to restore it are two very different levels of confidence — this lesson does both, for real.

A real backup

BACKUP DATABASE bookshop TO DISK = '/var/opt/mssql/data/bookshop.bak';

Processed 808 pages for database 'bookshop', file 'bookshop' on file 1.
Processed 2 pages for database 'bookshop', file 'bookshop_log' on file 1.
BACKUP DATABASE successfully processed 810 pages in 0.114 seconds (55.475 MB/sec).

810 pages, written to a real .bak file inside the container — not a description of what a backup does, an actual one.

A real disaster

DROP TABLE books;

SELECT COUNT(*) FROM books;

Msg 208, Level 16, State 1, Server ..., Line 1
Invalid object name 'books'.

The table is genuinely gone — not simulated, not a transaction that could be rolled back. DROP TABLE is not transactional recovery territory; this is what backups are for.

A real recovery

ALTER DATABASE bookshop SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE bookshop FROM DISK = '/var/opt/mssql/data/bookshop.bak' WITH REPLACE;
ALTER DATABASE bookshop SET MULTI_USER;

Processed 808 pages for database 'bookshop', file 'bookshop' on file 1.
Processed 2 pages for database 'bookshop', file 'bookshop_log' on file 1.
RESTORE DATABASE successfully processed 810 pages in 0.071 seconds (89.073 MB/sec).

SET SINGLE_USER WITH ROLLBACK IMMEDIATE disconnects everyone else and clears the way; WITH REPLACE permits overwriting the existing (now table-missing) database; SET MULTI_USER reopens it for normal use afterward.

SELECT COUNT(*) FROM books;


-----------
       5004

(1 rows affected)

5004 — every book back, including the four test rows inserted earlier in this track (the backup was taken after those inserts, before the DROP TABLE). The entire cycle — backup, genuine data loss, full recovery — actually ran, not just described.

Practice

1. Check backup history from database metadata instead of trusting memory.

SELECT CAST(database_name AS VARCHAR(15)) AS db,
       backup_start_date, backup_finish_date, backup_size
FROM msdb.dbo.backupset
WHERE database_name = 'bookshop';

db              backup_start_date       backup_finish_date      backup_size
--------------- ----------------------- ----------------------- ------------
bookshop        2026-09-13 18:13:46.000 2026-09-13 18:13:46.000      6709248

msdb.dbo.backupset is SQL Server’s own record of every backup taken — the reliable way to confirm “did the backup job actually run” instead of assuming a scheduled task succeeded.

2. Confirm the restore brought back the whole database, not just the one dropped table.

SELECT COUNT(*) AS audit_rows FROM price_audit;
SELECT COUNT(*) AS trigger_count FROM sys.triggers WHERE parent_id = OBJECT_ID('books');

audit_rows
-----------
          2

trigger_count
-------------
            1

price_audit (from the stored-procedures-and-triggers lesson) and trg_book_price_audit itself both came back too — RESTORE DATABASE operates on the entire database as a unit, not table-by-table, so recovering from DROP TABLE books also silently restored every other object exactly as it existed at backup time.

3. Confirm SET SINGLE_USER is genuinely necessary by considering what it protects against.

Without forcing single-user mode first, RESTORE DATABASE ... WITH REPLACE against a database with active connections fails rather than risk overwriting files mid-use by another session — this is a safety check, not busywork. ROLLBACK IMMEDIATE specifically means any open transaction from another connection is rolled back immediately rather than waited on, which matters in a real incident where waiting for other sessions to finish naturally could take an unknown amount of time.

That closes this track’s current pass. The thread through every lesson: SQL Server tells you exactly what it did and why — logical reads, Msg 530, a genuinely dropped audit row, a blocked session’s wait_type — reading that output closely is the actual skill, not memorizing syntax.

A real backup

A real disaster

A real recovery

Practice

Frequently Asked Questions