63

What is the best way to version control my database objects? I'm using Visual studio 2005/2008 and SQL server 2005. I would prefer a solution which can be used with SVN.

Vijesh VP
  • 4,508
  • 6
  • 30
  • 32
  • Try this free tool: [http://servantt.com](http://servantt.com/?so) - it allows you to reverse engineer your objects, save to scripts, compare database to scripts, launch WinMerge for comparison, update scripts or apply changes to the database. – drizin Jan 28 '16 at 20:48
  • Similar question http://stackoverflow.com/questions/115369/do-you-use-source-control-for-your-database-items/328307#328307 – Michael Freidgeim Nov 10 '16 at 19:08

10 Answers10

33

Same as your other code, add a "Database project" to your application solution and keep the sql files used to build the database objects in there. Use the same version control for those code files as you do for the application.

Ron Savage
  • 10,923
  • 4
  • 26
  • 35
  • If you use ssms to extract your db objects to source files, beware what encoding is used. It's possible that unicode may be used without you knowing it - it least that's what I've experienced. It can be annoying when you want to make a quick change via a text editor and you get gibberish. – Steve Nov 23 '09 at 17:07
  • 14
    @Steve, your editor is garbage if it can't handle Unicode. – CMircea Apr 13 '10 at 03:47
  • @Steve - I can't imagine why you would ever want to use anything other than Unicode nowadays. This is November 2009, not 1989! – Jeffrey L Whitledge Jun 30 '10 at 14:12
  • Gets interesting when you have differently named linked servers across dev, test, ... – adolf garlic Aug 04 '10 at 13:25
  • 2
    @adolf - which is a great reason not to do that. :-) In situations where I can't control the unfortunate architectural decisions made before me - I try to make my SQL "build scripts" with "if exists" blocks based on the environment (dev,test, etc.) and/or using variables with dynamically built SQL to handle changing DB, Link or Table names between environments. The goal being that the same SQL gets executed on all environments with minimal differences. – Ron Savage Aug 04 '10 at 21:28
  • I have used a similar technique in the past where the scripts have tokens replaced based on the build environment. Not ideal but it works. – adolf garlic Aug 05 '10 at 16:21
  • @Steve, but thank you making sure we're aware of that. I don't understand why everyone here has to be so rude all the time. – xr280xr May 15 '12 at 15:19
15

Look at the tools offered by RedGate. They specifically deal with backup / restore / comparison cases for SQL Server objects including SP's. Alternately I am not sure but I think that Visual Studio allows you to check sp's into a repository. Havent tried that myself. But I can recommend RedGate tools. They have saved me a ton of trouble

Thomas Wagner
  • 2,482
  • 1
  • 15
  • 7
  • 5
    I'm the product manager for SQL Source Control, which is available to try in early access. It works with SVN and integrates with SSMS. Sign up at http://www.red-gate.com/Products/SQL_Source_Control/index.htm . Comments welcome! – David Atkinson Feb 22 '10 at 23:43
  • 1
    SQL Source Control 1.0 http://www.red-gate.com/products/SQL_Source_Control/index.htm has now been shipped and is available to evaluate/purchase. – David Atkinson Jun 30 '10 at 12:17
  • A free alternative to RedGate is this free application: http://servantt.com/?so It's very simple to use, but does the job very well. – drizin Jan 28 '16 at 19:07
5

I use SVN for all of my table/sproc/function source control.

I couldn't find anything that met my needs so I ended up writing a utility to allow me to dump the code out into a nice directory structure to use with SVN.

For those interested, the source is now available at svn://finsel.com/public/VS2005/GenerateSVNFilesForSQL2005.

Josef
  • 7,431
  • 3
  • 31
  • 33
  • Hi Josef, I've registered and tried to download your tool as it looks great but I get the error "Could not find file 'C:\DotNetNuke\Portals\0\Repository\InstallSVNFilesForSQL2005.3a21c4ac-399a-4006-9057-d9f409e13ce3.zip'." from your site? Is there any chance you will release the source for this? – Kieran Benton Mar 07 '09 at 12:33
  • Soon as I get my site back up, I'll post the code at my public SVN. – Josef Mar 10 '09 at 19:12
  • @Josef - I can't access your svn server or get to your code - is it still active? Can I get the code somehow? – Conrad Mar 09 '12 at 21:29
  • Another alternative is this free application: http://servantt.com/?so – drizin Jan 28 '16 at 19:03
3

We use Subversion and all we do is save the sql code in the directory for our subversion project and then commit the code to the repository when we are ready and update from the repository before we start working on something already in there.

The real trick is to convince developers to do that. Our dbas do that by deleting any stored proc (or other database object) that isn't in Subversion periodically. Lose stuff once and pretty much no one does it again.

Jeffrey L Whitledge
  • 58,241
  • 9
  • 71
  • 99
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Your DBAs will love this free application, which does exactly what they are doing manually: http://servantt.com/?so – drizin Jan 28 '16 at 19:04
  • Yes, currently doing the same way. But I'm looking for something automatically integrates well with the changes in database schema, view and stored procedure. I think code first Entity Framework will be better in maintenance for the whole project(code + db). – sky91 Oct 09 '19 at 06:54
1

I don't know of a pre-packaged solution, sorry...

... but couldn't you just a little script that connected to the database and saved all the stored procedures to disk as text files? Then the script would add all the text files to the SVN repository by making a system call to 'svn add'.

Then you'd probably want another script to connect to the DB, drop all stored procedures and load all the repository stored procedures from disk. This script would need to be run each time you ran "svn up" and had new/changed stored procedures.

I'm not sure if this can be accomplished with MS SQL, but I'm fairly confident that MySQL would accommodate this. If writing SVN extensions to do this is too complicated, Capistrano supports checkin/checkout scripts, IIRC.

oz10
  • 153,307
  • 27
  • 93
  • 128
1

Best way - one which works for you.

Easiest way - one that doesn't currently exist.

We use a semi-manual method (scripts under source control, small subset of people able to deploy stored procedures to the production server, changes to the schema should be reflected in changes to the underlying checked in files).

What we should do is implement some sort of source control vs plaintext schema dump diff ... but it generally 'works for us' although it's a really faff most of the time.

Unsliced
  • 10,404
  • 8
  • 51
  • 81
1

I agree that if possible, you should use database projects to version your db along with your application source.

However, if you are in an enterprise scenario, you should also consider using a tool to track changes on the server, and version those changes. Just because the database project exists doesn't mean some admin or developer can't change those sprocs on the server.

Brian Vander Plaats
  • 2,257
  • 24
  • 28
0

We do dumps to plaintext and keep them in our VCS.

You'd be able to script a backup-and-commit to do something similar.

Oli
  • 235,628
  • 64
  • 220
  • 299
0

I'm using scriptdb.exe from http://scriptdb.codeplex.com/

And it might be usefull to use the rails way: http://code.google.com/p/migratordotnet/wiki/GettingStarted

Andreas Rehm
  • 2,222
  • 17
  • 20
0

Use versaplex for dumping your schema: http://code.google.com/p/versaplex/

Versaplex comes with Schemamatic, which reads database schema (tables, SPs, etc) and also data (data is dumped as CSV). I use it, with SVN and git, and it's awesome :) If you need help let me know, it's worth a try! http://github.com/eduardok/versaplex

Eduardo
  • 7,631
  • 2
  • 30
  • 31