8/17/2009

SQL Maintenance blooper

Sometime back I received a call from one of my clients. Client was having some system setup related issue that required a SQL Maintenance to be done. I quickly guided him over phone to go to MS Dynamics GP | Maintenance | SQL and select the required tables. Prior to this, I asked him if he kept a backup of DYNAMICS database and the answer was Yes. However, the system administrator of the client being new to the Dynamics GP environment (which I came to know only later) selected all tables by mistake and hit Drop Tables. Worst part was that there were users working online at this time. More worst was that after this happened, the new system administrator realized he doesn’t have a proper backup. Needless to say, the damage was real quick. All the users working online have been kicked out. There are several companies linked to the DYNAMICS database that got totally messed up. What would I do in this scenario?
I realized there must be some way out. Then, came a flashing idea. Why can’t we try hooking up any old(not so old) copy of DYNAMICS to this. Started searching for the same in the server directories and could find a copy of DYNAMICS that was 2 months older. Client was lazy enough to keep proper backups. I restored this DYNAMICS database to the existing database. Yes, it worked. However, minor data loss in the form of say, a new user record created recently was not there, so, we could quickly create the same. Similar but minor issues like some users who are assigned modified reports security wasn’t out there so have to set it up. Finally, things are back up and running. I just told myself “Oh! Boy..Today is your day!”
Why am I sharing this out here? Is to just let my readers know how scary it can be sometimes,  SQL Maintenance, if not done without having proper backups.Which is why I always tell my clients to keep proper data backups prior to doing any SQL maintenance. Specially, these are certain tasks that are recommended to be done with the help of either the Partners or experienced System administrators unless otherwise trust me, You got to pay for it!

UPDATE : Please do see a word of caution on linked Notes from David Musgrave in the Comments. Added on 18/08/2009

Please do see a similar experience shared by Doug, David Musgrave and Mariano from the link in the comments. Added on 19/08/2009