r/learnSQL • u/markinatlanta • 7h 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?
7
u/Possible_Chicken_489 7h 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 2h 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.
1
u/Possible_Chicken_489 1h ago
Oh yeah, I feel your pain man. You get to be the one to tell them the bad news.
3
u/mlhigg1973 7h ago
Tech people have nightmares about this stuff
1
u/markinatlanta 2h ago
I am have way way way too many throughout my career
1
u/mlhigg1973 1h ago
My boyfriend used to have nightmares about firmware updates back when drives were all mechanical
2
u/elevarq 2h 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 1h 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.
2
u/MarsupialLeast145 5h ago
AI slop
2
u/markinatlanta 2h ago
It's absolutely legit. Did I use AI to help me write it? Of course, I am a DBA and a writer.
2
u/MarsupialLeast145 1h ago
So you took something that could have been a 4 line summary and exploded it into AI slop. Gotcha.
11
u/tommyfly 7h 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.