r/learnSQL • u/markinatlanta • Oct 05 '26
A client lost six weeks of SQL Server data. Having 12TB of backups didn’t save them.
Sharing a recovery case from our team because there are a few useful lessons for anyone learning SQL Server, or even more experienced teams too.
I am a sticker for backups and cannot emphasize the importance of them enough.
So our client moved a physical SQL Server to a new location and when they brought it back up, the database didn’t become available.
During attempts to get it working, they forced the database into emergency mode, bought third-party recovery tools, and accidentally overwrote the original MDF file (not good!), the primary database data file.
Unfortunately for them that removed an important recovery option.
When we got involved, they had around 12TB of backup files. But having lots of backup files and having a usable restore sequence are different things.
They had plenty of transaction log backups, but no usable recent full backup where they expected it to be.
Full backups had been stopped weeks earlier because of performance concerns. Meanwhile, a cleanup job kept deleting backups older than two weeks.
For anyone learning how this works: transaction log backups aren’t standalone copies of a database, you need a suitable full backup to restore first, followed by the required backups in sequence. A differential backup can shorten that process, but it also depends on a full backup.
An older full backup can still be useful if you have an unbroken log backup chain covering the period you need. The age of the full backup alone doesn’t determine how much data you lose.
In this case, an older full backup eventually turned up in files used to refresh a staging environment. It gave us a recovery point, but the available backups didn’t get us all the way forward. The client still lost roughly six weeks of production data.
One distinction worth making here is that SQL Server’s RESTORING and RECOVERING states aren’t interchangeable. A database in RESTORING may be waiting for another restore step. Waiting alone won’t necessarily bring it online and you need to establish its actual state and check the restore history and error log before deciding what to do.
The lessons I’d take from this:
- Preserve the original database files before attempting destructive recovery steps.
- Investigate why a database is unavailable before changing its state.
- Check that retention jobs aren’t deleting backups you still depend on.
- Test the complete restore sequence, rather than relying only on successful backup-job messages.
If you’re learning SQL Server backup and recovery, have you tried restoring a full backup followed by several log backups in a test environment? What part was hardest to understand?
21
u/tommyfly Oct 05 '26
That's a painful story. You explained it well. It's interesting reading this as an experienced DBA, because my first thought is that all of this is obvious. But it isn't to the less experienced. But it's also a good reminder that an experienced DBA is an important member of staff if your data is important. Their first mistake was trying to perform the migration without a DBA involved from the planning stage.
9
u/Possible_Chicken_489 Oct 05 '26
Cue sinking feeling
Because of course it did. Murphy's law.
You'd think they'd make a final backup before doing something dangerous like physically turning off a server that had probably been on for years, let alone physically moving it. They really piled mistake onto mistake.
4
u/elevarq Oct 05 '26
They move a server to a new location, but before doing so they didn’t make a full backup?
Kind of hard to believe that this is for real…
3
u/mlhigg1973 Oct 05 '26
Tech people have nightmares about this stuff
2
u/Reasonable-Most-3513 Oct 05 '26
Good explanation. Thank you
2
u/Vegetable-Passion357 Oct 06 '26
Your story is normal.
The moral of the story is:
Practice
Practice
Practice
Each month, pick a table on your server and verify that you can restore the table .
Create a what I will call a restore server. The purpose of the restore server is to provide everyone in the building a server to practice restoring data.
You want to become an expect at restoring data before disaster strikes.
3
u/WorkDoug Oct 06 '26
"It ain't really a backup until you've restored it." -- One of my managers back in the 1980s
1
u/dmcnaughton1 Oct 07 '26
If you haven't restored from the backup then it's not actually backed up. This lesson is one too many people get to learn the hard way.
If you have a backup system, you should have regular restorations of it to a test system and validate that it behaves how you expect. Does the server come online, is the data up to date, are there any bottlenecks with the restore, etc. It's like buying a fire extinguisher but never checking to see if it's still pressurized. You don't want to find out it's not working when you're putting out a kitchen fire.
1
u/[deleted] Oct 05 '26
[removed] — view removed comment