Back up a SQL Server database in SSMS to a .bak file, break it, then restore it back — that is this whole post, in that order. We take a full backup of the DemoDb database from the previous tutorial, delete a row and corrupt another one on purpose, then restore the backup over the damage and prove the data is back. After that we restore the same file a second time as a copy, side by side with the original. Every step has its own real screenshot, and the two restore failures we hit on the way — the ones you will hit too — are shown exactly as they appeared, with their fixes.

Versions used in this tutorial

  • SQL Server 2025 (17.x) — Enterprise Developer edition (free for development and testing), build 17.0.1000.7
  • SQL Server Management Studio 22.11.3
  • Demo database: DemoDb with the table dbo.DemoCustomers — the same database the previous post connected to Visual Studio

The screenshots were captured on a Windows Server 2022 machine; the SSMS clicks are identical on Windows 10 and 11 desktops.

Before you start: set up your environment from zero

Nothing here assumes a running server. If you worked through the previous post, you already have all of this — skip to the verify step.

Step A — Install SQL Server 2025 (Enterprise Developer, free)

  • On Microsoft's SQL Server downloads page, choose Developer → Enterprise Developer. New in SQL Server 2025, there are two free Developer editions: Enterprise Developer (every Enterprise feature) and Standard Developer (every Standard feature). Both are free — no key, no subscription — licensed for development and testing, never production.
  • Run setup → Installation → New SQL Server standalone installation.
  • At Instance Configuration, keep the default instance (this tutorial uses a default instance; a named instance works the same, you just type its name when connecting).
  • At Database Engine Configuration → Authentication Mode, keep Windows authentication mode — it is all this tutorial needs.
  • Click Add Current User so your Windows account is a SQL Server administrator, then Install.

Step B — Install SSMS 22

Download SQL Server Management Studio 22 (this tutorial uses 22.11.3) from Microsoft — a separate free tool — and install it. Backup and restore live in SSMS, not in Visual Studio; administering the server is SSMS's job.

Step C — Create the backup folder and the demo database

  • Create a folder for backups: C:\VSL-Backups. One honest warning that saves confusion later: when a backup runs, it is the SQL Server service account — not your Windows login — that reads and writes this folder. On a default local install that just works; if a backup ever fails with an access-denied error, it is the service account's permission that is missing (see Errors #5).
  • Open SSMS, connect to your instance, open a New Query window and run setup.sql from the companion code folder (linked at the end): 
CREATE DATABASE DemoDb; GO USE DemoDb; GO CREATE TABLE dbo.DemoCustomers ( CustomerId INT IDENTITY(1,1) PRIMARY KEY, FullName NVARCHAR(100) NOT NULL, City NVARCHAR(100) NOT NULL ); INSERT INTO dbo.DemoCustomers (FullName, City) VALUES (N'Amara', N'Toronto'), (N'Jonas', N'Berlin'), (N'Priya', N'Lagos'); SELECT CustomerId, FullName, City FROM dbo.DemoCustomers;

Step D — Verify everything

Two checks. First, the setup script's SELECT returns three rows — Amara in Toronto, Jonas in Berlin, Priya in Lagos. That grid is the before picture; the whole tutorial is about getting back to exactly it.

Second, check the database's recovery model — one setting that decides whether a checkbox in Step 13 even exists:

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'DemoDb';

On a default install this returns FULL, because new databases inherit their recovery model from the model system database, and model ships as FULL. DemoDb here is FULL. If yours says SIMPLE, everything in this post still works except the tail-log part of Step 13 — under SIMPLE that section is not offered at all, and there is nothing to uncheck. Now the backup.

Part A — Take the full backup (Steps 1–7)

Step 1 — Connect, and find DemoDb in the tree

Open SSMS and connect to your instance. On this machine the server name is localhost (a default instance on the same PC), with Windows Authentication. SSMS 22's Connect dialog sets Encrypt to Mandatory — and on a local instance you will typically tick Trust Server Certificate, the same self-signed-certificate story as the previous post's Visual Studio error.

Expand Databases and find DemoDb → Tables → dbo.DemoCustomers. If the table is there, your setup is real and Part A can start.

Step 2 — Right-click DemoDb → Tasks → Back Up…

Backups start from the database itself. Right-click DemoDb, hover Tasks, and click Back Up… — the first item in the Tasks submenu.

Step 3 — Read the Back Up Database dialog once, properly

The dialog opens on its General page, and four fields decide everything:

  • Database: DemoDb (the database you right-clicked).
  • Recovery model: FULL — read-only here; it is the setting you checked in Step D.
  • Backup type: Full. A full backup copies the entire database. The other types — differential and transaction log — only make sense standing on a full backup, which is why we take this one first.
  • Backup component: Database (whole database, not selected files).

Step 4 — Set the destination: your own folder, your own file name

Under Destination, SSMS pre-fills a backup file inside the instance's own Backup folder. Remove it. Click Add…, and in Select Backup Destination type the full path of your file:

C:\VSL-Backups\DemoDb-full-2026-11-05.bak

The .bak extension is a convention, not a rule the engine enforces — but every tool and every colleague expects it, and the dialog itself tells you to use it. A dated file name pays off the first time a folder holds more than one backup.

Step 5 — Optional but worth it once: press Script and read what the dialog really does

Before clicking OK, try the Script button at the top of the dialog (Script → Script Action to New Query Window). SSMS writes the exact T-SQL your clicks amount to, into a query window:

BACKUP DATABASE [DemoDb] TO DISK = N'C:\VSL-Backups\DemoDb-full-2026-11-05.bak' WITH NOFORMAT, NOINIT, NAME = N'DemoDb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Two options in that statement matter for later. NOINIT means append: if the file already exists, the new backup is added to it as another backup set rather than replacing it. The opposite choice — starting a fresh media set — is the Overwrite all existing backup sets option on the dialog's Media Options page (FORMAT in T-SQL). Appending is the default and it surprises people at restore time (Errors #8). The companion folder's 01-backup-full.sql is this statement, plus an optional RESTORE VERIFYONLY line that checks the finished backup set is complete and readable.

Step 6 — Click OK and read the confirmation

Close the extra query window if you opened one, click OK on the Back Up Database dialog, and wait for the confirmation:

Step 7 — Prove the file exists, on disk, with a size

Open C:\VSL-Backups in File Explorer. The .bak file is there — on this machine 3,636 KB, written at the minute the dialog confirmed. A backup you have not seen on disk is a rumour; this one is a file.

Part B — Break the database, on purpose (Step 8)

Step 8 — One wrong UPDATE, one DELETE

A restore you have never watched fix real damage is a restore you merely believe in. So we do the damage deliberately — the two accidents that happen in production every week: a value overwritten with a wrong one, and a row deleted outright. In a new query window on DemoDb, run 02-break-it.sql:

USE DemoDb; UPDATE dbo.DemoCustomers SET City = N'Munich' WHERE FullName = N'Jonas'; DELETE FROM dbo.DemoCustomers WHERE FullName = N'Priya'; SELECT CustomerId, FullName, City FROM dbo.DemoCustomers;

The SELECT now returns exactly 2 rows: Amara untouched, and Jonas — in Munich, a city he was never in. Priya's row is gone. Keep this query window open; in about two steps it becomes the villain of the piece.

Part C — Restore over the damage (Steps 9–15)

Step 9 — Start from the Databases node, not from the database

Here is the classic wrong turn, made by almost everyone the first time: restore does not start by right-clicking the database. Right-click the Databases node — the folder above all your databases — and choose Restore Database…. Starting at the node is what allows restoring over an existing database and restoring under a brand-new name (Part D) from the same dialog.

Step 10 — Source: Device → add your .bak file

On the General page, under Source, select Device (not Database), click the … button, and in Select backup devices click Add. Browse to C:\VSL-Backups\DemoDb-full-2026-11-05.bak and OK your way back. The devices dialog now lists your file as the backup media — the restore will read its sets from here.

Step 11 — Check the destination and the backup set

Back on the General page, SSMS has read the file and filled in the plan:

  • Source Database: DemoDb (read from the backup itself).
  • Destination Database: DemoDb — the existing, damaged database. This one field is the whole difference between restoring over a database and restoring as a copy: type a different name here and Part D happens instead.
  • Backup sets to restore: one row — DemoDb-Full Database Backup, type Full — ticked. If this grid ever shows more rows than you expect, stop and read Errors #8 before clicking anything.

Note the yellow banner at the top of the dialog: “A tail-log backup of the source database will be taken. View this setting on the Options page.” SSMS is warning you about Step 13 already. Good — we will deal with it there, deliberately.

Step 12 — Files page: where the restored files will live

Open the Files page and read the grid, even if you change nothing. Each row is one file inside the backup: its logical name (DemoDb for the data file, DemoDb_log for the log), where it originally lived, and Restore As — where it will be written. For a restore over itself, Restore As points at the database's current files, which is exactly right. This grid is also the T-SQL WITH MOVE clause wearing a dialog's clothes — Part D edits it, and Errors #4 is what happens when the paths point somewhere that does not exist.

Step 13 — Options page: the three switches this whole tutorial turns on

Open the Options page. As the dialog first opens it — and this is verified on the screenshots from this tutorial's own session — the page arrives like this: Overwrite unticked, Close existing connections unticked, and Take tail-log backup before restore ticked. All three defaults are wrong for what we are doing. Set them deliberately:

  • Overwrite the existing database (WITH REPLACE): TICK it. You are knowingly replacing DemoDb's current contents with the backup's. The T-SQL name tells the truth: REPLACE bypasses SQL Server's built-in safety checks, so it should only ever be ticked when overwriting is the actual intention — here, it is.
  • Close existing connections to destination database: TICK it. A restore needs the database to itself. Any open connection — an application, another SSMS window, the Step 8 window still sitting on DemoDb — blocks it. Ticking this makes SSMS flip the database to single-user, restore, and flip it back to multi-user for you.
  • Take tail-log backup before restore: UNTICK it. A tail-log backup captures the log records written since the last backup — the tool for rolling a database forward to the latest possible moment before a restore. We are doing the opposite: travelling back to the backup, throwing the later changes (our damage) away. A tail-log backup here is not just pointless — on a restore under a new name it can flip the original database into the Restoring state (Errors #3). Leave Recovery state at its default, RESTORE WITH RECOVERY — it brings the database back online, ready to use, when the restore finishes.

What actually went wrong twice, before this restore worked

This tutorial's own session hit the restore failure twice before the clean run above — same error both times, two different causes. Both dialogs are real, captured live, and they are the two failures you are most likely to meet:

Attempt 1 — Overwrite unticked. We expected the textbook safety error here (error 3154 — see Errors #1). We never reached it. The restore failed earlier, with “Exclusive access could not be obtained because the database is in use” — because the Step 8 query window was still connected to DemoDb. Lesson: the in-use check fires before the overwrite safety check. An open window beats a checkbox.

Attempt 2 — Overwrite ticked, Close existing connections unticked. Same error, verbatim. With Overwrite now on, the only remaining blocker was the connection itself. (Notice, in the full session, that the tail-log backup was also still ticked at this point — three wrong switches, peeled off one at a time.) The fix for both attempts is the Step 13 setting above: Close existing connections. Switching your own query window's context to master first — USE master; — helps too; the session did both before the clean run.

Step 14 — Click OK: restored successfully

With the three switches set, click OK. The restore runs (a small database is seconds) and SSMS confirms:

Step 15 — The restore-proof: three rows, and Jonas back in Berlin

Never trust the success dialog alone — verify the data. Run 05-verify.sql:

USE DemoDb; SELECT CustomerId, FullName, City FROM dbo.DemoCustomers;

Three rows. And check the value, not just the count: Jonas is back in Berlin, and Priya's row exists again. The restore did not just bring back a row count — it rolled the database back to the exact moment the backup was taken, undoing an UPDATE and a DELETE in one move. If your grid ever shows three rows but Jonas still in Munich, you restored the wrong backup set — Errors #8.

Part D — Restore the same backup as a copy (Steps 16–18)

Same file, different destination: a second, independent database next to the original. This is how you inspect a backup without touching production data, hand a developer a realistic copy, or park a before-snapshot next to a risky change.

Step 16 — Repeat Steps 9–11, but type a new name: DemoDbCopy

Right-click Databases → Restore Database…, Source Device, add the same .bak — then on the General page, change Destination Database from DemoDb to DemoDbCopy. Typing a name that does not exist yet is the entire trick; watch the dialog's title change to match.

Step 17 — Files page: rename the Restore As paths

Open the Files page before clicking anything else. The backup's files want to land on the original database's file names — DemoDb.mdf and DemoDb_log.ldf, which are in use. Rename Restore As for both rows, keeping the same folder:

...\MSSQL\DATA\DemoDbCopy.mdf ...\MSSQL\DATA\DemoDbCopy_log.ldf

Then open the Options page and untick Take tail-log backup here as well — for a restore under a new name this is the dangerous one (Errors #3). Overwrite stays unticked: DemoDbCopy does not exist, so there is nothing to overwrite, and SSMS does not ask for it.

Step 18 — Click OK, then look at the tree

Click OK. When it completes, refresh Databases in Object Explorer: DemoDb and DemoDbCopy, side by side. Open a query window on DemoDbCopy and run the same SELECT: the same three rows, in the copy — while the original was never touched by this second restore.

The T-SQL form of Part D (in 04-restore-as-copy.sql) shows what the Files page edits became — note the logical names, straight from Step 12's grid:

RESTORE DATABASE [DemoDbCopy] FROM DISK = N'C:\VSL-Backups\DemoDb-full-2026-11-05.bak' WITH MOVE 'DemoDb' TO 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\DemoDbCopy.mdf', MOVE 'DemoDb_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\DemoDbCopy_log.ldf', RECOVERY;

Backup types and recovery models, in one minute

Everything above was one full backup — the whole database in one file, the foundation every other backup type stands on. The other two, now that a full backup exists:

  • Differential backup — only the changes since the last full backup. Restoring means the latest full plus the single latest differential — never a chain of them.
  • Transaction log backup — the log records since the last log backup; a chain of them is what makes point-in-time restore possible. Available only under the FULL or BULK_LOGGED recovery models — under SIMPLE, the log is truncated automatically and log backups do not exist, which is also why the tail-log section vanishes from the Options page.

Choosing a recovery model is choosing how much work you can afford to lose: SIMPLE trades recoverability for zero log management; FULL keeps every log record so you can restore to a point in time, at the price of taking log backups so the log does not grow forever. And the one-line habit that outranks all of this: a backup is not a backup until you have restored it once — which, if you followed along, you have now done twice.

Errors you may hit

1. Error 3154 — “The backup set holds a backup of a database other than the existing 'DemoDb' database”

The safety check doing its job: you are restoring over an existing database without Overwrite (WITH REPLACE), and SQL Server refuses to silently replace one database with another's backup. The full dialog form, from Microsoft's own SSMS documentation example, reads: “System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Sales' database. (Microsoft.SqlServer.SmoExtended)” — with your database's name in place of Sales. Fix: if overwriting is genuinely the intention, tick Overwrite the existing database (WITH REPLACE) on the Options page. If it is not the intention, stop — this error just saved you. (One sequencing truth from this tutorial's session: if anything is still connected to the database, you will meet Error #2 first — the in-use check fires earlier.)

2. “Exclusive access could not be obtained because the database is in use”

The error this tutorial actually hit — twice, in the Part C callout above. A restore needs exclusive access; something is connected: an application, a service, or your own SSMS query window still pointed at the database (guilty). Fix: tick Close existing connections to destination database on the Options page — SSMS flips the database to single-user, restores, and returns it to multi-user — and switch your own query windows to master (USE master;) before restoring. The T-SQL equivalent of the checkbox is in 03-restore-overwrite.sql.

3. The tail-log trap — two faces

Face one: you are restoring to go back to a backup (our Part C), and Take tail-log backup before restore is ticked — as it is by default under the FULL recovery model. The tail-log backup preserves the very changes you are trying to undo, and if its file cannot be written, the restore fails before it starts. Untick it when travelling back. Face two (the dangerous one): restoring under a new name with the tail-log option left on can leave the original database stuck in the Restoring state — Microsoft's restore documentation walks through exactly this scenario, including the “the tail of the log for the database has not been backed up” error that accompanies it. Untick it in Part D as well. The rule that covers both: a tail-log backup belongs to rolling forward before a restore, never to going back or sideways.

4. Restore As paths point at a folder that does not exist

The Files page's Restore As values come from where the files lived on the machine that made the backup. Restore a backup from another server — or rename carelessly in Part D — and the paths can point at folders that are not on this machine, and the restore fails on file creation. Fix: correct the paths on the Files page (or use WITH MOVE in T-SQL, exactly as 04-restore-as-copy.sql does). Also make sure the new file names do not collide with an existing database's files.

5. Access denied on the backup folder

Backup and restore run as the SQL Server service account, not as you. A folder your login can open can still be closed to the service — network shares are the classic case — and the failure only appears when the operation actually runs. Fix: grant the service account read/write on the backup folder, or put the file somewhere it already has access (the instance's own Backup folder always qualifies).

6. “The backup was taken on a newer version” — restoring down-level fails

Backups only travel one way: a backup taken on SQL Server 2025 cannot be restored onto SQL Server 2022 or older — the restore fails on the version mismatch. Older-to-newer works (restoring a 2022 backup onto 2025 succeeds and upgrades the database's internal format as part of the restore). Plan migrations accordingly: you can move up with backup/restore, never down.

7. Differential or Log backup type is unavailable

Two ordinary causes. No full backup exists yet — differentials and log backups stand on a full backup; take one first (Part A). Or the database is under the SIMPLE recovery model, where log backups do not exist at all. Check the model with the Step D query; if the work needs log backups, the database needs FULL (and a log-backup routine to go with it).

8. The restore grid shows the wrong backup set

Remember Step 5's NOINIT: backing up to the same .bak file again appends a second backup set instead of replacing the file. Restore from that file later and the Backup sets to restore grid lists every set in the file — pick by the backup date and position columns, and make sure the tick is on the set you mean. Restoring last month's set over today's database is how a routine restore becomes an incident. (The devices dialog's Contents button lists a file's sets without starting a restore.)

Practice exercise

Try it yourself (10 minutes, code in the companion folder):

  • Run setup.sql, take the full backup with 01-backup-full.sql, then break the data with 02-break-it.sql.
  • Restore over the damage using the SSMS dialogs (Steps 9–15) — deliberately leaving Close existing connections unticked the first time, with your damage window still open. Read the error. Then set the three Step 13 switches properly and restore clean.
  • Run 05-verify.sql and confirm the proof properly: three rows, and Jonas in Berlin.
  • Bonus: restore the same file as DemoDbPractice with 04-restore-as-copy.sql — remember to rename the Restore As paths (or the WITH MOVE targets) first.

Reply with your backup file's size in KB — I read every one.

FAQ

Do I need a full backup before a differential or log backup?

Yes. A differential captures changes since the last full backup, and a log chain starts from one too. No full backup, nothing for the others to stand on — which is why Part A comes first.

Can I restore a SQL Server 2025 backup onto SQL Server 2022?

No — restores only move to the same or a newer version (Errors #6). Going down a version means scripting the schema and moving the data, not backup/restore.

Does restoring a database overwrite it without asking?

No. Without Overwrite (WITH REPLACE), SQL Server's safety checks stop a restore that would replace an existing database (Errors #1). REPLACE is a deliberate act — the dialog makes you tick it, and T-SQL makes you type it.

Is a verified backup (RESTORE VERIFYONLY) a proven backup?

It proves the backup set is complete and readable — worth doing, and 01-backup-full.sql includes it. It does not prove the restore itself will succeed on your target, with your paths and your permissions. The only full proof is a test restore — Part D is exactly that, pointed at a copy.

Get the code

The companion folder has every script from this post, in run order: setup.sql, the backup statement SSMS generated (01-backup-full.sql), the deliberate damage (02-break-it.sql), both restores as T-SQL (03-restore-overwrite.sql, 04-restore-as-copy.sql), and the restore-proof SELECTs (05-verify.sql):

https://github.com/sajjad037/visual-studio-learn-code/tree/main/topic-08-backup-restore-ssms

Sources — the official Microsoft Learn pages behind this post

Every procedure and error above traces to Microsoft Learn (accessed November 4–5, 2026):

Disclosure: Visual Studio Learn is reader-supported. Some links may be affiliate links, which means the channel may earn a small commission at no extra cost to you. Only tools actually used in the tutorials are ever linked, and affiliate relationships never change a verdict.