More often than i’d like to admit, i have started to make changes in a stored procedure (or any other random act of programming) and only eventually realized that 1) it was a bigger deal than i throught, and 2) it no longer works. And i REALLY HATE to walk away from code this isn’t working.
The obvious (ha!) solution is to clone my USP before making changes (modify, change alter to create, change the name, execute the code) , but sometimes that is too simple to work for me. (“harder, harder, must make it harder…”).
The general solution is to think about source control or version control. Unfortunately, since the document of interest, the USP, is stored inside of the MS SQL database, it is difficult to use traditional subversion software to back them up. There are a couple of expensive solutions for this, and i GUESS i could go to my boss and say that all of us should use something and that he should buy it and reward me for being the first person to notice there was a problem. Guess whose problem THAT would be?
Other workarounds would be to copy\paste from the console into a text document on my local machine and use traditional source control from there, or script\save the stored procedures as a .sql file on my local machine. There is a script wizard that would do that, but i haven’t learned how to script or automate it, and t-sql output seems limited to 4000 odd characters for display. And i got the oddest stuff if i output the results of querying the description property (the text) to a file.
What i am leaning towards is a low-cost hybrid solution. Anup Saund has a project on gitHub called .gitSQL. The free version saves only 4 item each object type, the $40/year version is unlimited. What it does is to interrogate your sql server, list the databases, let you pick one, and shows you the stored procedures, tables, views and functions. You can select all or some of them, and export them to your local machine. I had some issues with the initial import. Silly me checked the “import new” button, and that was a mistake. It also took a long time to initially read the database, but now it comes back up fast.
What you get on your local machine are .sql files that will, if imported back to the server, recreate the object. This script is familiar, since you could generate it with “script to > create” in SSMS. But this file also contains the entire code that you would be editing locally, so you can just cut/paste into SSMS if the current code is not running.
So right now i have four files that correspond to four USPs from Data_2. Anytime i am going to work on a stored procedure, i can put an updated local copy on my machine, or backup ALL the USP scripts. However – there is something else – the local script contains not only the code for the USP, it also contains any code to create stored procedures that your stored procedure references, any output tables (at least the schema) and other assorted code. Since i am going to want to just copy\paste a working copy of the code, this is not a real problem for me.
Secondly, i have also installed GIT on my machine, and the .gitSql destination is a’ git inited’ directory. So i open .gitSQL and a GIT client (GIT BASH is going to be fine), and each time i get nervous, i can GIT ADD and GIT COMMIT to source control.
This means 30 seconds or so of work at various stages of my editing. i don’t see this as a major issue – every 10-20 minutes or so i SHOULD take a 30 second break anyway.
I can also back up SQL tables – create schema and import data in the .sql file or just the data into a flat file. Not good for backups, but fine for utility tables, such as tables2process.
This just might work for me — and maybe i can claim it as a tax deduction.
Dummies Guide to SQL for THIS dummy 