Back in April, I ran across what I consider to be a horrendous piece of code in a stored procedure. This code cost me several hours of after hours work on a Saturday night plus wasted the time of at least four other people who were involved in this particular project. It also caused the project to be set back by 2 months.
Back then, we were looking to upgrade a piece of software we use. The database back end for this software was a huge 500 GB database. Before doing the upgrade, which was to occur on a Saturday night, I was asked to create a backup of the database. A full backup of this database takes 13 hours, so I made a full backup Friday night and planned to make a differential Saturday night. Once the differential was done, I was to email some other people and they would proceed with the upgrade.
Backups here are done using a home-grown stored procedure. This procedure is rather nice in that it checks to see if the server it is running on has LiteSpeed installed. If it does, it uses LiteSpeed to create the backup and if not, if uses native SQL commands. You pass in a parameter to specify if you want to perform a full, differential, or transaction log backup.
Friday night, the full backup was made without incident. Saturday night rolled around and I launched the procedure, requesting it to make a differential backup. Based on past history, I expected this to take anywhere from one to two hours. This is what I communicated to the other people on the project and they had planned their night accordingly.
Two hours passed and the backup was still running. Three hours passed. Still running. It was now approaching midnight. I open a new query window in SSMS and queried sys.dm_exec_requests to check on the progress. I was shocked to see that backup was only about 20% complete. I launched activity monitor and looked at the command that was being executed. It was making a full backup! I checked my job again to make sure I specified a differential and, sure enough, I had. Yet the procedure was making a full backup.
At this point, I decided to check the stored procedure and got my second surprise of the evening: I discovered the stored procedure was encrypted! At the time, I was still fairly new at this company and didn’t know where I might find the source code for the procedure and since it was midnight on a Saturday, I didn’t have many options. I contacted the rest of the team and told them what was going on. The decision was made to abort the upgrade and reschedule.
When I got back into the office the following Monday, I talked to the other DBA about this. He said he knew the stored procedure was encrypted but didn’t know why. It was something that was done by a previous DBA who was no longer with the company. But he was able to point me to the source code. I reviewed it and found the following little nugget:
IF @FullDifTran = 'dif' AND DATEPART( dw, getdate()) = 7 SET @FullDifTran = 'full'
If the procedure is called requesting a differential backup and the day of the week is Saturday, the procedure ignores what the user requested and makes a full backup.
This cost my company about 20 man hours. And since the software company’s support team wasn’t available again for another 2 months, we lost two months on this project.
Never never never write code like this!!! Your code should never change what the user told it to do. If you are trying to protect against the user doing something, then print a message or throw an error and abort and make the user resubmit with an additional flag to indicate “Yes, I really want to do this.” But never change the command in the background without any sort of warning!