A few years ago I was working for a bill payments company. We were using checkfree's software for our presentation of bills and we wrote our own bill payments engine. The database was MS SQL Server and we were using log shipping to send data from the master to the backup database server. It was using EJB 2.0 and batch processing was pretty slow, so I decided to delete all the transaction data from my sandbox database except for the first month's data to test something. The only problem was that I typed the delete command in the sql window connected to the production database.
A few minutes later one of the utility companies called and said they could not see any transactions. My face went red. I actually felt the blood rush to it. I went to the DBA to ask if she could restore the database from the last back up only to find out that they did not know they were to back up that database. So I asked if we could change the backup database server to the become the master only to find out that the transaction logs had already been shipped to that database and that had the same data as the master.
I thought I was definitely going to get fired or go to jail. Fortuanately I found this program called Log Explorer that let me create insert statements for all the transactions deleted before the boss found out. It cost me a couple hundred dollars for a trail version but it was worth it. I checked at www.lumigent.com . They don't seem to make it anymore. Needless to say, I have been very weary of getting write access to anything in production ever since.
All of this could have been avoided if we:
- Secured the database server so that only the application could write to it.
- Have more than one person look over the plan of what is to be backed up.