Git And SQL Server MDF Files

Question

OK..so I'm new to Git / Github after being on Visual Source Safe most of my career and have seen the light - love it. So I have migrated all my .NET projects to my Github account and would like to also manage all my SQL Server databases with Git as well. In all my projects I always put my database files in a /Database subdirectory so I have, for example, /Databases/MyDatabase.mdf and /Databases/MyDatabase.ldf in my source tree. I am tracking these files with Git and they show in Github nicely with all the rest of my source.

Here's what I was expecting to happen: I stop SQL Server with a NET STOP MSSQLSERVER and I checkout a branch so Git will pull down the MDF and LDF files for that branch. Restart SQL Server with NET START MSSQLSERVER and do whatever work I need to do on the source and database for that particular branch. Git would track my changes to the MDF file and when I do a stage / commit / push it would send the changes back up to the remote repository.

I tried this by pulling down the database and adding a new column to a table and doing a commit. Git told me that there were no changes to any items which I was not expecting...I changed the MDF file. Is it not possible for Git to track changes in an MDF file? My first guess was that maybe because it was binary and not text base Git might have trouble with it but I believe it's possible to use Git to keep track of image files and other binary items so that doesn't seem like it would be the issue. Any ideas? Is it just not possible? Should I not even be trying to do this? Thanks in advance for your comments.

Answer 1

It is very bad idea to place changing database files into any source control. Try to create/update appropriate scripts and store them in source control - this is the proper way to track schema changes inside the DB

If you're using Visual Studio - this is a good point to start with database and server projects in it. But sometimes it behave in weird manner, so use with caution and gently

OR you can use some commercial/free software to track the changes inside DB schema and data, like RedGate Schema Compare or Redgate Data Compare

Answer 2

Have you considered using our SQL Source Control tool to keep track of development changes. This does all the 'scripting out' for you behind the scenes. It actually uses SQL Compare's engine under the hood.

http://www.red-gate.com/products/sql-development/sql-source-control/

As Oleg correctly points out, it's possible to track schema changes using SQL Compare and SQL Data Compare, but here at Red Gate we wouldn't recommend you do this over maintaining your development environment under source control. Ideally you should do both. Grant Fritchey has written an excellent article describing how SQL Compare's command line can be used with a source control system to track schema changes. He uses SourceGear Vault in his examples, but the principles apply to any source control system.

http://www.simple-talk.com/sql/database-administration/auditing-ddl-changes-in-sql-server-databases/