Git And SQL Server MDF Files

Question

OK .. I am new to Git / Github after I have been at Visual Source Safe for most of my career and have seen the light — I love it. So I migrated all my .NET projects to my Github account and would also like to manage all my SQL Server databases using Git. In all my projects, I always added database files to the / Database subdirectory, so I have, for example, /Databases/MyDatabase.mdf and / Databases / MyDatabase.ldf in my source tree. I track these files using Git, and they render well on Github with all the rest of my source.

Here's what I expected: I will stop SQL Server using NET STOP MSSQLSERVER and I will branch out, so Git will pull out the MDF and LDF files for that branch. Reboot SQL Server using NET START MSSQLSERVER and do whatever work I need to do for the source and database for this particular branch. Git will track my changes in the MDF file, and when I do the / commit / push step, it will send the changes back to the remote repository.

I tried this by pulling out a database and adding a new column to the table and doing a commit. Git told me that I did not expect any changes in any elements ... I changed the MDF file. Can't Git track changes to an MDF file? My first guess was that maybe because it was binary and not the Git text base it might have problems with this, but I believe that you can use Git to track image files and other binary elements so that it doesn't look it will be a problem. Any ideas? Is it impossible? Should I even try to do this? Thanks in advance for your comments.

Answer 1

It is a very bad idea to place the modified database files in any source control. Try to create / update the appropriate scripts and save them in the original control - this is the right way to track schema changes inside the database

If you are using Visual Studio, this is a good time to start working with databases and server projects. But sometimes it behaves strangely, so use with caution and caution.

OR you can use some commercial / free software to track changes within the database schema and data , such as RedGate schema comparison or Reddate Data Compare

Answer 2

Have you considered using our SQL Source management tool to track development changes? This makes all the “scripts” for you behind the scenes. It actually uses the SQL Compare engine under the hood.

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

As Oleg correctly points out, you can track schema changes using SQL Compare and SQL Data Compare, but here, in Red Gate, we do not recommend that you do this while maintaining the development environment under source control. Ideally, you should do both. Grant Frichey wrote a great article that describes how to use the SQL Compare command line with the source control system to track schema changes. He uses SourceGear Vault in his examples, but the principles apply to any version control system.

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