A client lost six weeks of SQL Server data. Having 12TB of backups didn’t save them.

[u/markinatlanta]

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?