DallasDBAs.com

SQL Server Database Consulting

backup

SQL Server Backups: The Basics

by Leave a Comment

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 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.

    • 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

 

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!

https://pocket-dba.com/

Book a call, and mention “Newsletter”

Thanks for reading!

— Kevin

Why Your SQL Server Backups Aren’t Enough

by Leave a Comment

SQL Server backups are database insurance – you don’t really appreciate them until disaster strikes. But here’s the kicker: having backups doesn’t mean your data is safe. If you’re not regularly doing test restores, your backup strategy is a ticking time bomb.

 

The False Sense of Security

Many IT leaders and system admins think, “We have full backups every night. We’re covered.” But when the time comes to restore, they discover:

· The backup file is corrupt.
· The storage location is inaccessible.
· The restore process takes way longer than expected.
· The recovery model wasn’t configured properly.
· The point-in-time restore doesn’t actually bring back the data they need.

At that point, it’s not a “backup strategy.” It’s a data loss incident.

Why Restores Must Be Tested

SQL Server backups are only as good as your ability to restore them. If you’re not proactively testing restores, you’re just hoping they work when disaster strikes. Hope is not a strategy.

• Test different restore scenarios: Full, differential, and transaction log restores.
• Validate integrity with DBCC CHECKDB after restores.
• Ensure RTO and RPO align with business needs. (Translation: Can you restore fast enough to avoid a business meltdown?)
• Automate restore tests so you’re not guessing when the pressure is on.

Automating Restore Tests with dbatools

If you’re not testing restores because it’s “too time-consuming,” dbatools.io has your back. The Test-DbaLastBackup command automates the process by:

1. Restoring the last backup of a database to a test environment.
2. Running DBCC CHECKDB to ensure data integrity.
3. Reporting on success or failure, so you know where issues exist before you actually need the backup.

Example Command:
Test-DbaLastBackup -SqlInstance YourSQLServer -Databases YourDatabase

This takes a lot of manual effort out of the equation—no more excuses for not testing!

Action Plan for SQL Server Resilience

1. Schedule regular test restores (weekly or monthly) and document the process.
2. Use DBCC CHECKDB to verify database integrity post-restore.
3. Leverage Test-DbaLastBackup to automate restore verification.
4. Confirm backup files are accessible and stored securely offsite.
5. Train your team on recovery processes—because backups are useless if no one knows how to restore them.
6. Monitor backup success, but more importantly, track restore success.

The Bottom Line:

A backup plan without restore testing is a disaster waiting to happen. Don’t wait until you’re knee-deep in downtime to realize your backups weren’t as solid as you thought. Test your restores. Validate your data. Sleep better at night.

Thanks for reading!

–Kevin

New Pocket DBA® clients get the first month FREE!

Get a Free SQL Server Health Check!

 

 

Restore Database or Restore Log?

by Leave a Comment

How long has this worked, and why didn’t anyone tell me?

A typical restore statement from a FULL backup and a LOG backup:

USE [master]

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' 
    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 25, REPLACE

RESTORE LOG [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' 
    WITH  FILE = 1,  NOUNLOAD,  STATS = 25

GO

Results:

100 percent processed.
Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE DATABASE successfully processed 354 pages in 0.016 seconds (172.393 MB/sec).
30 percent processed.
60 percent processed.
90 percent processed.
100 percent processed.
Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE LOG successfully processed 27 pages in 0.011 seconds (18.909 MB/sec).

Completion time: 2022-05-11T09:26:44.3023396-05:00

Change the Restore Log to Restore Database:

USE [master]

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' 
    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 25, REPLACE

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' 
    WITH  FILE = 1,  NOUNLOAD,  STATS = 25

GO

Results:

100 percent processed.
Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE DATABASE successfully processed 354 pages in 0.015 seconds (183.886 MB/sec).
30 percent processed.
60 percent processed.
90 percent processed.
100 percent processed.
Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE LOG successfully processed 27 pages in 0.006 seconds (34.667 MB/sec).

Completion time: 2022-05-11T09:30:42.0225244-05:00

I had no idea the engine would determine the type of backup file and apply it appropriately. I knew it worked for Differential backups, but not Log.

20+ years and I still find new (to me) things every day.  Technically Jon (t) found it but hey…we’re a team here 🙂

Thanks for reading!

Kevin3NF

Ola log backup job failure

by Leave a Comment

I had a log backup job that was not failing, and was also not backing up logs:

EXECUTE [DBA].[dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES. model',
@Directory = 'D:\Backups\KBH-Precision_SQL2016',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

Older version of Ola. Newer versions give this:

Date and time: 2021-09-28 14:56:12
Server: KBH-PRECISION\SQL2016
Version: 13.0.5026.0
Edition: Developer Edition (64-bit)
Platform: Windows
Procedure: [DBA].[dbo].[DatabaseBackup]
Parameters: Removed for cleanliness
Version: 2020-12-31 18:58:56
Source: https://ola.hallengren.com
The following databases in the @Databases parameter do not exist: [USER_DATABASES. model].
Date and time: 2021-09-28 14:56:12
Completion time: 2021-09-28T14:56:12.6474426-05:00

 

Watch those typos, kids!

Thanks for reading!

Kevin3NF

TIL: Backup-DbaDatabase

by Leave a Comment

(This post comes from one of our Junior DBAs – @SQLDork)

I’m learning how to use dbatools.

Today’s command: Backup-DbaDatabase

I’m using the 64-bit PowerShell ISE (running as administrator), running PowerShell version 5.1 on Windows 10, with a local SQL 2016 install.

Here’s the code, each snippet adding additional parameters and building off the one before it, mostly, with a bunch of 100% hilarious comments.*

*Comments may or may not be 100% hilarious, on account of hilarity not being a measurable substance.**
**Probably.

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test

This one works, yaaaay! All it does is take a full backup of one database. We can do better, probably.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Diff

This takes a differential backup, but calling your diffs .bak is bad™

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff

-FilePath lets us set the extension, but in doing so, we lose timestamps. I wonder if there’s a way to fix that… (Spoiler alert: there totally is)

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff -CompressBackup

Also figured out how to compress backups, but it doesn’t work if you try to take a compressed diff after an uncompressed full.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff -FilePath test3.dff -CompressBackup

This one worked, because science!™ Also backup chains, there’s probably a blog post around here somewhere explaining those, you should go find it and send it to all your friends!

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup -initialize

Using -initialize makes it overwrite the existing copy, nothing too exciting.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016

Bare minimum required to run without errors, takes full backups of every system and user database on the box.
Except for tempdb for some reason, idk why*
* I totally know why

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff `
-FilePath test3_timestamp.dff -CompressBackup -ReplaceInName

Using -ReplaceInName lets us use timestamps with reasonable file extensions
Welcome to people-not-yelling-at-you-because-they-can’t-tell-if-the-important-backup-file-is-a-full-or-a-log town!
Population: Us

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test, model

Use commas to take backups of multiple databases at once.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -confirm

Anything that’s a checkbox in the Command Add-On doesn’t need any fancy parameters, it just works™.
If you’re using the PS ISE. If you’re using notepad, stop it.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full `
-FilePath test3_timestamp.bak -ReplaceInName -buildpath -createfolder `
-path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup'

-Path tells the files where to go
-FilePath sets the name/extension of the file
-BuildPath tells PowerShell to make the path if it doesn’t exist
-CreateFolder sends all the files into nice little subfolders for the humans to look at and go “Hey look, subfolders!”.

And then they go drink coffee and complain about the database being “slow”.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Type full `
-FilePath dbname_timestamp.bak -ReplaceInName -ExcludeDatabase test -buildpath `
-path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\donuts'

-ExcludeDatabase has a very non-intuitive and confusing function: it excludes databases from being backed up.
Useful for the really big ones you only want to backup once a week or something.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test `
-FilePath dbname_timestamp.bak -ReplaceInName -FileCount 2

This makes multiple striped files, appending numbers automatically, as so:
[filename]-1-of-2.bak
[filename]-2-of-2.bak
[filename]-donuts-of-2.bak
Actually that last one was a joke, but that’s all I got for today, and probably the next month or so.

 

Follow me (b|t) on twitter, where I also probably won’t say anything for the next month or so.

 

Your DR Plan isn’t a Plan

by 9 Comments

 

My apologies, but someone had to tell you.

You don’t have a Disaster Recovery Plan™. You have a Disaster Recovery Hope. (paraphrased from a source I’ve forgotten…)

If I’m wrong (and I hope I am), its because you are in the 10% (optimistically) of companies that actually test their DR plans and document the results.

In the 18 years I’ve been asking the following question, I’ve gotten one excellent and correct response, and one that was good, but I still poked holes in it.

“If your most important database server melted right now, how long until you are back online?”

I get a lot of responses on this question:

  • 2 days
  • 30 minutes
  • I don’t know
  • Blank Stares (its a response…just a scary one)

If you are not testing your DR plan, you may be betting your company’s existence on a flawed premise: “Our people know what to do. We have backups.”

If your DR plan is a digital “living document” that lives on your Sharepoint server, you are already in trouble.  Everyone involved needs a current, non-digital copy on their desk and at their home.

Disaster recovery is about far more than restoring databases.  But, for my mostly-SQL Server audience, let me ask you this:

Can you do an emergency test restore of your most mission critical database onto identical hardware, to a point 27.5 minutes ago and have everything work as expected?

If you don’t do test restores, you are HOPING your backups are valid. If you don’t have the ability to replace critical hardware or spin up an identical VM, you are HOPING that your server or SAN never fails.

If you are running through your DR Plan regularly, with everyone ready to go and knowing their place in the process…you are HOPING the next disaster happens when everyone is available, online and up to speed.   What if that disaster happens when your Sr. DBA is on vacation?  Who is her backup? A Jr. DBA?

If you don’t know the answer to the “How long…” question above, you really should go find out who in your organization does.  If nobody does, contact me.

If you think you have it 100% nailed…I challenge you to let me into your server room unattended.

Thanks for reading!

Kevin3NF

Home Blog About Privacy Policy