Deployment and source-control-friendly database versioning scripts

One of the projects I’m on has over 2,200 stored procedures. Until I arrived, none of these were under source control — the ‘stable’ version was whatever was on the production database server, and the ‘trunk’ version was whatever was in the development database server (which incidentally wasn’t even backed up).

Anyway, I set about exporting all the stored procedures and checking them into source control. But as easy as it sounds, it actually took a few goes to get the it all right. You see, when generating a CREATE script with IF NOT EXISTS, SQL Management Studio has a habit of generating entire scripts as one giant EXEC sp_executesql string instead of just putting a GO in between. This isn’t very good for development — you don’t get intellisense, syntax highlighting, etc inside a string.

To get around this problem, I ended up generating two separate scripts — first a DROP (with IF NOT EXISTS), then a CREATE (without it) — and set SSMS to put each in the same file (Append to File). This gave me one script per stored procedure, each with an IF EXISTS DROP, GO then CREATE statement — perfect for source control.

Here are some other tips for configuring SQL Management Studio (via Options > SQL Server Object Explorer > Scripting) to make it automatically generate friendly scripts:

  • Disable Include descriptive headers. They sound helpful, but all they do is add the script date and repeat the object’s name. This is really annoying because it clutters up my results for source-code text search in Visual Studio.
  • Disable Script USE <database>. We often have several copies of the same database running with different names on the same SQL box (e.g. for different development branches). Hard-coding the name of the database in all your scripts means you have to manually change all the USE statements every time you run them. And trust me — the risk of having your changes leak over and get applied to the wrong database is not really something you want to be worrying about.
  • Enable Include IF NOT EXISTS clause. Stored procedures, triggers, functions — it’s much easier to just nuke and re-create them every time instead of worrying about the semantics of CREATE vs ALTER. This also means you can just run each script over and over and over again.

If you get it right, SQL Management Studio 2008′s Script DROP and CREATE should produce something that looks like:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Employee_Get]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Employee_Get]
GO

CREATE PROCEDURE [dbo].[usp_Employee_Get]
AS
	SELECT
		EmployeeID,
		FirstName,
		LastName
	FROM
		Employee

GO

We also put permissions (e.g. GRANT EXEC) at the end of each script, so they are restored each time the stored procedure is created. February 6, 2009


3 Comments

Kevin Berridge on February 6, 2009 at 2:36 pm.

Thanks, I found your post very interesting because I’ve been spending a lot of time thinking about this kind of stuff recently.

Do you script tables too? How do you handle table changes?

In development, do you edit the scripts and then execute them on the database? Or do you work directly on the database, then re-generate the scripts when you’re done?

Did you actually go through 2,200 Stored Procedures and generate a Drop and Create by hand, or did you find some way to automate it?

Richard on February 6, 2009 at 3:12 pm.

Kevin: we script table changes only, with hand-written migration scripts (I haven’t managed to convince my team to use a tool like Migrator.NET yet). Wherever possible, we try to write them in such a way that you can run them multiple times if required (e.g. wrapping CREATE statements with an IF NOT EXISTS block). Sometimes this is not possible however, e.g. dropping a column and moving data to another.

We generally work between SQL Management Studio (for editing/debugging) and Visual Studio (for checking in to the database project in source control). We cut and paste instead of regenerate scripts.

As for the 2,200 stored procedures, SQL Management Studio 2008 brought back the ‘script one object per file’ option missing from 2005. So I was able to dump them all automatically in two passes :)

David Compton on June 14, 2009 at 1:30 am.

Kevin,

Nice article. We have been using the same single file per object approach for version control purposes for some years now – but back on SQL 2000 where the default scripting option was to generate an “if exists…drop” followed by a “create”. I was struggling with how to get the drop and create into the one script using SQL 2008. Your suggestion for 2 passes with “drop” and “if not exists” options in the first pass and “create” in the second pass, along with the “append” option does the trick very nicely.

I find that it is best not to use the “if not exists” option with the second pass for “create” as that caused the object definition to be scripted as a string using the sp_executesql stored proc.

I also found it helpful to set up my default scripting parameters under Tools | Options | SQL Server Object Explorer in SSMS.

Leave a Reply