The install finished an hour ago and the server is perfect — and one disk failure away from being a story you tell other people. Thirty minutes of backup setup on day one is the cheapest insurance in IT.
Every other guide in this series can be redone: reinstall the server, re-pick the collation, re-open the port. Data cannot be reinstalled. A backup routine is therefore not a finishing touch on a SQL Server install — it is part of the install. This guide takes your first real backup, explains what kind you took, and sets up the schedule that keeps taking them after you have forgotten this page exists.
In SSMS, right-click your database, choose Tasks, then Back Up. Keep the backup type as Full, set the destination to your backup folder on a different volume from the data files, and run it. That .bak file contains the entire database — data, structure, everything — as of that moment. The same job in T-SQL is one line: BACKUP DATABASE SalesDB TO DISK = 'F:\SQLBackup\SalesDB_full.bak' WITH INIT, CHECKSUM;. The CHECKSUM option makes SQL Server write integrity data into the backup so corruption can be detected later — always include it.
A full backup is the whole database. A differential backup is everything changed since the last full — smaller and faster, useless without that full underneath it. Transaction log backups, available when a database uses the full recovery model, capture changes continuously and let you restore to a point in time — the difference between losing a night's work and losing fifteen minutes. For a small office, the honest starting pattern is a nightly full backup. Add log backups when the business decides that a day's data loss is unacceptable — that decision is about money, not technology.
Backups that depend on memory do not happen. On Standard edition and above, SQL Server Agent runs scheduled jobs, and SSMS's Maintenance Plan Wizard will build a nightly full-backup job (plus cleanup of old files) in a few clicks — use it, and set the Agent service to start automatically. Now the honest exception: Express edition has no Agent at all, so maintenance plans cannot run. The Express pattern is a small .sql script executed by Windows Task Scheduler through sqlcmd every night. It is less pretty and completely reliable — but you must build it deliberately, because nothing in Express will remind you.
Be blunt about the geometry: if the database and its backups share one physical drive, a drive failure, a ransomware infection or a stolen server takes both copies in the same second. Backups must leave the building, at least logically — copied nightly to another machine, a NAS or an external drive that is rotated, ideally with one copy offline or off-site. The 3-2-1 idea applies: three copies, on two different media, one somewhere else. Our storage guide set the backup folder on its own volume for exactly this reason; this guide's schedule must include the step that copies it away.
A backup you have never restored is a hope, not a backup. Once, on day one, prove the chain: restore last night's file to a different database name (SSMS restore dialog lets you rename it, for example to SalesDB_test), open a few tables, confirm the data is real, then delete the test copy. RESTORE VERIFYONLY checks a file's readability and checksums, which is good — but only a real restore checks you: your permissions, your paths, your understanding. Schedule a restore test a few times a year; the day you need it for real is the wrong day to learn how it works.
Watch the video for the full install — and put the backup routine in place the same evening, while the server is still new and empty.
▶️ Watch Full VideoNightly full backups are the sane minimum, with backup files kept for at least two weeks. If losing a day of entries would hurt badly, add transaction log backups every 15 to 60 minutes under the full recovery model — that combination restores to almost any minute you choose. The right frequency is set by how much re-typing the business can bear.
In SSMS, expand SQL Server Agent, then Job Activity Monitor, or right-click the job and view its history. For maintenance plans, the plan's history shows the same record. Make checking it a weekly habit, and consider configuring Database Mail or a simple log file review so a failed job becomes visible instead of silent.
A full backup is designed to run online: users keep working while it runs, though a very large database on a busy server will add some disk load. That is why schedules point at quiet hours. Never 'back up' a database by copying its .mdf file from Explorer while the service is running — that file is open and changing, and the copy is not a valid backup.