Saturday, 5 May 2012

Why you need UseSandboxSettings

At MITIE we use Visual Studio Database projects to automate our database builds. This usually works perfectly, however recently we discovered that the build was deleting all of the data from one of our development databases. We had backups so we didn't lose any data, but it was inconvenient to say the least!

Suspicion first fell on the 'Always recreate database' option in the Database.sqldeployment file, however this didn't appear to be set - which created something of a puzzle. After a bit of poking around we realised that the problem stemmed from the use of 'My isolated development environment' in the project properties;

This setting is intended to enable developers to build and deploy the database project to their own machines using their own settings as it explains here. However, the .dbproj.user file that stores individual developers' settings was being stored in source control, hence the automated build was using these settings for the shared development environment. The solution was two-fold. Firstly we removed the .dbproj.user file from source control, and secondly we added the "UseSandboxSettings=False" option to our msbuild script, examples of which can be found here. This msbuild option ensures that user settings are ignored by the build process, whether they are included or not. I'd recommend doing this for all builds on shared development environments.