Problem:
The 4 internal SQL server databases that manage the information about client databases hosted by SQL Server 2005 ("master" databases) became corrupted. When we couldn't recover the corrupted databases, we tried restoring from the nightly backup routine. Upon restoring, we found that the backups contained no new information past August 1st, 2008.

Cause:
The underlying cause of this was two similarly-named database files in use on the same server. DB1 and DB2, for example. During maintenance, these database files were swapped, DB1 going into DB2's place and vice versa.

Data entry then continued on DB2 (the wrong file), and the nightly backup routines backed up DB1 instead. No new data was saved; the backups were essentially frozen in time.

When the "master" databases broke down, the backup/restore jobs for all of the SQL databases kicked in and began restoring all databases. Restoring DB1's older data over DB2. Erasing new data input to DB2 in the process.

The problem here wasn't so much the corrupted internal database; it was the lack of up-to-date backups in the restore. This wasn't discovered until it was too late, however.

Solution:
At this point, the server was shut down for repair. We moved the available backups to a new SQL server. Once the new server was in place, we re-input data changes since August 1st, using employee backups.

Recommendation: During database maintenance, files can be moved around on SQL servers. Files with similar names, even if stored in different locations, can cause complications like this. Check all database names to avoid confusion in the future.

________________________________________
Tech Note Written by: Brent Taylor & Will Pritchard, PlanetMagpie W3 Services
November 21, 2008