Recently I have started a new job, and one of my first tasks is to help improve the database development and deployment process. They are moving away from having all changes managed in the dev database, and pushing changes using SQL Compare. Thankfully they have already started this process, with database delta scripts being written and deployed using DbUp. This can work quite well and is a technique I have used for managing and deploying databases previously.
The next step to improving the process is having all stored procedures, functions and views scripted out of the database and living as part of the codebase. Any changes made should be made to the script, which now has a history in source control, and all of these scripts should be applied to the database with each deployment.
SQL Server Management Studio does provide tools to assist with this, but I found it took me a while to get all the right settings to export things the way I wanted, in a way that would allow DbUp to repeatedly apply these changes without error.
Right click on the database, select tasks, Generate scripts
Select which ones you want (procs, functions, views, etc)
Save to file, single file per object, and specify the target folder. I have a separate folder for stored procs, views, and functions, and I export each separately.
Ensure ANSI text is selected so git can diff the files easily. This is what tripped me up the first time.
Click Advanced.
Set Script USE DATABASE = false
For stored procs:
Check for object existence = true
Script CREATE
This creates CREATE + ALTER scripts for all procs. (You don't need to script drop). This has the advantage of not dropping existing procs, in case you have custom permissions created on the procs.
For views and functions, we want to do something similar, but scripting CREATE with checking for existence generates the create as a string, which is ugly, harder to maintain, and only creates it once, rather than altering the view / function once it is created. I believe this is due to some of the intricacies of how views and functions are handled by SQL Server, whereas you can easily create a dummy stored proc and immediately alter it.
Here is an example:
This is obviously not that useful. But we can't always drop and create, as this won't work if the view / function does not exist (initial run), and we can't always create, as this is not re-runnable.
SO: Do 2 generates. One for only the DROP, checking for existence.
Second for the CREATE, not checking for existence. Untick overwrite, and in advanced, set Append to file = true.
This gives you a nice set of files, one per view or function, that includes a conditional DROP if exists, and then a create, so is re-runnable.
For a good overview of how to integrate these newly exported scripts with your projects and have DbUp run them on every execution, go here: http://wengier.com/reviewable-sprocs/
Top comments (3)
Super great write-up! We're exploring DBUp as a proof of concept at my company and this step by step of generating idempotent scripts from sp's out of our db into something more maintainable for use with DBUP was exactly what I was looking for - thank you!
Thanks for linking to my post :)
I am working on a project that has tons of stored procs. Does this method generate objects in order of their dependency so I can just dump them to a folder and run dbup?