Imagine that you have some important year end procedures in your SQL Server database. Or maybe you have some important, but rarely run code. No matter what the circumstance, if this is a large amount of code, it might be difficult to remember how it’s structured or even how it works on different sets of data.
Now, imagine that you have a malicious individual that thinks like Andy Mallon talks about in this post. They run an ALTER PROCEDURE and replace the entire procedure body with “RETURN 0”. Now imagine that you don’t find out about this until months later when the important code is run. That would be a bad day, and certainly one that might have you struggling to recover the code from backups. Do you have months of old backups? I used to, in a place where we kept an end of month backup from every month for a year. I always had the previous 12 months, but I wouldn’t want to restore them all to find out where code got changed.
In most places, we don’t have that many backups, and we often assume we’d only go back a few full backups at most in the event of a disaster. I’ve also been a consultant for more than a few companies that found out that they had 2-10 full backups, all of which were corrupt and from the last few weeks. Sometimes there were older backups on dev/test machines, but in some cases, lots of data was lost.
It’s important to run DBCC to catch corruption, but it’s also important to keep track of your code. There might not be maliciousness, but simple errors in code that’s changed without comprehensive testing. This can be especially problematic for rarely run code that we don’t test as thoroughly and won’t detect issues for a significant amount of time.
Save your code in a VCS. Git is free. File|Save is in every editor on the planet. There’s no excuse not to use a VCS and save code. Redgate has tools to make this easy, but I worked in a file|save environment for years, and it worked. Learn about a VCS and build the habit to backup your code in a repository. It’s simple and easy, and you won’t know you need it until you do. At that point, it’s too late.