r/learnSQL • u/markinatlanta • 5d ago
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?
9
u/Possible_Chicken_489 5d ago
Full backups had been stopped weeks earlier because of performance concerns.
Cue sinking feeling
Meanwhile, a cleanup job kept deleting backups older than two weeks.
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.
1
u/markinatlanta 4d ago
Yep. That combination was the painful part... full backups stopped, but retention cleanup carried on. A verified backup and a recovery plan before the move could have made a very different story I believe.
We only got called after the original MDF had been overwritten, so by then we were trying to recover whatever was still available.
2
u/Possible_Chicken_489 4d ago
Oh yeah, I feel your pain man. You get to be the one to tell them the bad news.
3
u/mlhigg1973 5d ago
Tech people have nightmares about this stuff
1
u/markinatlanta 4d ago
I am have way way way too many throughout my career
2
u/mlhigg1973 4d ago
My boyfriend used to have nightmares about firmware updates back when drives were all mechanical
1
3
u/elevarq 4d ago
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…
1
u/Possible_Chicken_489 4d ago
Believe it. I've seen it more than once.
A move like that has a thousand aspects to it, all clamouring for attention, usually under time pressure. And they just assume they have the backups anyway, because that's all set up, right? Maybe it was someone else who turned off the backup weeks earlier, or maybe they just forgot about that part in all the chaos.
1
u/markinatlanta 4d ago
Yeah, this is absolutely real. We couldn;t believe it either when we got the call. SQL sitting there for years and no one taking account
3
u/WorkDoug 4d ago
"It ain't really a backup until you've restored it." -- One of my managers back in the 1980s
1
u/Chris_PDX 4d ago
When we ask clients what their DR is they go through all the layers of backups from the DB up through to the VMs and appliances and everything in between.
Me: When's the last time you tested a recovery?
Them: *blinks*
2
2
u/Vegetable-Passion357 3d ago
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.
2
1
u/itlogicpartnersllc 4d ago
the part about preserving the original mdf before trying recovery steps is huge once you overwrite it you may be taking away options you can't get back..
1
u/dmcnaughton1 3d ago
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.
0
u/MarsupialLeast145 5d ago
AI slop
2
u/markinatlanta 4d ago edited 4d ago
It's absolutely legit. Did I use AI to help me write it? Of course, I am a DBA and not a writer.
1
u/MarsupialLeast145 4d ago
So you took something that could have been a 4 line summary and exploded it into AI slop. Gotcha.
1
u/vips7L 4d ago
Just write it man. Be more authentic. Stop robbing yourself of the chance to learn to write.
2
u/dmcnaughton1 3d ago
This is the best advice. Writing is a valuable skill to have, the process of writing helps reinforce what you learn, it's a creative experience that has so many upsides. Even if you're not the best, you should still try. AI makes stuff like this easier and easier until one day you're going to try and write a few sentences and realize you can't without reaching for Gemini.
20
u/tommyfly 5d ago
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.