Git: my personal MS SQL server data time machine

I had to solve a bug that was caused by inconsistent state, persisted in MS SQL server. The bug could be reproduced. But it did cost some effort and you had to start over again once the bug had manifested itself to reproduce it again.

Git to the rescue. The solution was simple: with a few git commands, I created my own MS SQL server time machine. I added the raw SQL data to a Git repository, and now I can not only go back and forward, but I can also create branches, to mimic specific situations. I can now reproduce the bug, do my analysis, revert, reproduce again, etc. Very nice!

Here's a description of how to do this.

• Stop sql server (via SQL management studio or using services.msc)
• Go to C:\program files\Microsoft SQL Server\MSSQL.1
• Right-click on the folder "MSSQL" and choose "git bash here"
• git init
• git add .
• git commit -a
• Enter a nice comment (with vi: press i, enter your comment, press escape, press :wq)
• Create a branch for the data that is needed for this bug and switch to this branch: git checkout -b newbranchname. In my case: git checkout -b bug-1577
• Start sql server again

Now I made the changes to get to the situation where I could reproduce the bug. To save this state, do the following.

• Stop sql server
• Go back to the git bash on C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL
• git status if you are a control freak (or just curious)
• git add . if you like the newly added logs and traces
• git commit -a with appropriate comment
• Restart sql server

Now you can go ahead and test the data or bug. If you want to restore the situation, just do the following:

• Stop sql server
• git checkout -f = undo all local modifications
• Start sql server

These last steps can be automated with a simple batch file:

net stop mssqlserver
git checkout -f
net start mssqlserver

There is a downside. It costs a lot of diskspace because SQL server seems to change each and every database every time. So each commit will add the entire data directory to git again. You can avoid this by comitting only invidual files. But, on the other hand, your time is probably more costly than disk space.

Once you get to know Git, you can't do without it any more.