Every developer working with databases and backend magic needs DB source control. If you think you don’t, then you are crazy, or will become crazy soon after you realize it. But not every developer has the money to pay for the awesome RedGate SQL Source Control tool (~$1500 per developer, or ~$6000 for a team of 5). Sometimes, the ramen noodles expression must be interpreted literally. So how do you resolve/implement this problem? You may want to follow K. Scott Allen’s database version control blog series (links below).
- Three rules for database work
- The Baseline
- Change Scripts
- Views, Stored Procedures and the Like
- Branching and Merging
These posts are great for the conceptual background and practices when doing the change scripts and such. The problem is there is no much implementation in the series to help speed up the process. To complement the series, you can DOWNLOAD my implementation of SQL Source Control. It consist of roughly of 4 tables and 4 sprocs. If you want to implement it on your SQL Server Database, download this update script and run it against your database (make a backup first, just in case).
Here is the ERD of the version control entities:
To use it, just create your change script (maybe follow some of the advice from Scott Allen), and follow this template to add the final touches to the script. Essentially all you have to do is call the Database_Update sproc and inspect the DatabaseErrorLog table whenever you encounter a problem to find more details.
[sourcecode language="sql"] BEGIN TRY
DECLARE @DatabaseVersionLogId INT , --This is for later, you may needed if you want to report to a CI Server. @UpdateFromDatabaseVersion VARCHAR(32) = '1.2.3' ,--The database version you want to update. This is your starting point. @UpdateToDatabaseVersion VARCHAR(32) = '1.2.4' ,--The new version of the database after successfuly updated @UpdateScriptName VARCHAR(64) = N'''1.2.3 to 1.2.4''' ,--A friendly name for this script @UpdateScriptFileName VARCHAR(1024)= N'''123 to 124.sql''' ,--The file name containing this script. @CreateScriptName VARCHAR(64) = N'1.2.4' ,--A friendly name for ANOTHER script (not this one) that generates the new schema from scratch as opposed to from the @UpdateFromDatabaseVersion database @CreateScriptFileName VARCHAR(1024)= N'124.sql' ,--The file name containing the @CreateScriptName script @Tags VARCHAR(MAX) = N'1.2.4, 1.2.3, super release'--A CSV for tagging and searching your releases later on.
-- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION
EXEC @return_value = [dbo].[Database_Update] @UpdateFromDatabaseVersion, @UpdateToDatabaseVersion, @UpdateScriptName, @UpdateScriptFileName, @CreateScriptName, @CreateScriptFileName, @Tags, @DatabaseVersionLogId = @DatabaseVersionLogId OUTPUT
SELECT @DatabaseVersionLogId AS N'@DatabaseVersionLogId'
PRINT ( 'All good. Update finished... now go open a beer' )
PRINT ( 'Michael Jackson''s moonwalk. The script is a piece of shit. Fix it and try again. Your database is safe.' )
END CATCH [/sourcecode]
You can download the same script HERE
Enjoy and Happy coding!