- Accidental DBA
- Posts
- SQL Server Backups: The Basics
If you’re responsible for a SQL Server instance, you need working, consistent backups. Not just a .bak file here and there, but a plan that runs automatically and covers the full recovery cycle.
Here’s how to get that in place, even if you’re not a DBA.
Understand What Each Backup Type Does:
You don’t need them all every time, but you do need to know what they’re for:
· Full Backup
A complete copy of the entire database at that moment in time. It’s your foundation.
· Differential Backup
Captures only what changed since the last full backup. These can help speed up recovery time and reduce storage needs. Not really necessary if your databases are small.
· Transaction Log Backup
Captures everything written to the transaction log since the last log backup. Needed for point-in-time recovery.
o If your database is in Full or Bulk-Logged recovery model and you're not doing log backups, your log file will grow endlessly, potentially filling the drive it is on.
Set a Backup Schedule That Works
For production databases, this is my minimum recommended setup:
· Full backups once per day
· Log backups every 5 to 15 minutes
· Optional differentials every few hours for large databases
For dev/test databases:
· Full backups daily or weekly are usually fine
· You can skip log backups unless you're testing recovery processes. If you are going to skip, set the databases to SIMPLE Recovery
Automate the Backups
Use SQL Server Agent to schedule the jobs. Here are two options:
Maintenance Plans (basic, GUI-driven)
Good for smaller environments or shops without scripting experience
Be careful, default plans may not have the best options for your situation
Included in SQL Server, supported by Microsoft.
Ola Hallengren’s Maintenance Solution (highly recommended)
Free, open-source, script-based
Handles full/diff/log backup rotation, cleanup, logging, and more
Optionally does corruption checking and index/stats maintenance
Use SQL Agent to schedule the process via the jobs the script created
Free, FAQ/email/community support, but not Microsoft
Store Backups Somewhere Safe
Don’t store them on the same drive as the database files. If the drive dies, the data and backups may both be lost
Better options:
Separate disk or volume
Network share
Azure Blob Storage or S3, via Backup to URL option
Monitor It
Make sure the backup jobs are:
Running successfully
Completing on time
Not overwriting too soon or growing endlessly
Use SQL Agent alerts, third-party tools, or scripts to monitor backup age and job success.
The Bottom Line:
Understanding the basics of what backups are, and how they work is KEY to protecting your company’s most valuable asset. If you don’t know how this works and it is your responsibility, a database failure without a backup could be a career limiting move.
New Pocket DBA® clients get the first month FREE!
Book a call, and mention “Newsletter”
SQL tidBITs:
Backing up a VLDB? Use options like MAXTRANSFERSIZE and multiple TO DISK targets (e.g., file1.bak, file2.bak) to reduce runtime and I/O impact. This can be critical when you’ve got limited backup windows.
Reply