DallasDBAs.com

SQL Server Database Consulting

backup

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

Installing the ‘Ola Scripts’…quick and easy database maintenance

by 1 Comment

I was recently in a conversation about the best way to go about setting up maintenance (Backups, Integrity Checks, Indexes and stats) for a group of SQL Servers, with minimal hassle, and easy to deploy to new servers.

The factors that came into play on this were:

  • Supportability
  • Cost
  • Ease of use

We discussed the following different options (I was not talking to a DBA, but a SQL Developer):

For this customer, in this environment, I decided to recommend Ola’s scripts.  The primary drivers were ease of installation and the amazing free support from the hundreds (thousands?) of DBAs that know and love them.  Myself included.

But I still have to prove my point to this client no matter what I recommend…so I made this video.

Enjoy:

Skip to 5:00 if you already have SQL Server installed…that first bit is just to show this on a clean instance 🙂

If you have any questions, feel free to comment on the video, or if you need specific help hit up #sqlhelp on Twitter.

Thanks for reading and watching!

Kevin3NF

Home Blog About Privacy Policy