We recently took on a new client with an old SBS 2003 server hosting an EPOS database in SQL 2000. The client went to their outgoing support provider for handover details (usernames/password etc) and the next morning they came in to find their EPOS database had data from several key tables deleted. Coincidence? Quite possibly, who am I to comment.

We immediately went to site to try and find out what was going on, only to find that there had been no backups taken of either the server or the database or the logs since 2010. Not great news. The client was getting ready to start manually reentering the data whilst I went off to research any possible way of recovering the data. Everything I read said that without some kind of backup there was little hope. Not one to give up I thought there must be a 3rd party tool to help after all I had the database in its current state and a huge *.LDF file.

I found a possible solution in APEX SQL Recovery and got it installed after faffing about with dot.net 3.1. Using the online database and the LDF file, APEX SQL Recovery went through and managed to recover around 10 of the 500 odd entries in one table. Not good enough (and this may have been because it was a trial) but it gave me hope that the data was held in the transaction log LDF.

A bit more researching and I found a forum post from way back when, recommending some software called  SQL Log Recovery from RedGate software. Sure enough, after processing the 1.9GB *.LDF file, SQL Log Recovery produced a breakdown of every transaction that has taken place on that database since that backup in 2010. and a very handy ‘undo transaction’ button. Looking through the log I could see that the offending data had been deleted over a 2 hour period the night before, there was a bout 2000 delete transactions. I selected a couple of the transactions and took note of the data they showed. Pressed the magic button and the software created a script that I could run directly from the program or copy into SQL Query Analyzer. I let it do it’s thing and then checked the actual database. Sure enough the missing data was back, exactly as it was.

After a laborious process of individually selecting 2000 rows, in batches of 200, and running the scripts the database was back to where it had been the night before. Disaster averted, miraculously. SQL Log Recovery is only compatible with SQL 2000. Needless to say the client was very happy and we made a great first impression, but they had learnt some serious lessons:

• Backup, Backup & Backup – make sure you do your own or see evidence that your support provider is. doing them for you.
• Test your backups. So many organisations are proud to say they take backups, but if you ask ‘…and do you test them?’ the usual answer is no!
• Make sure your support provider gives you all the information required for a handover at the start of the contract (we include everything in our welcome pack).
• Use a support provider that just won’t give up until a solution is found – just like us!