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.


Emil Glownia said...

Hi Phil,

Thanks for sharing it with everyone. I thought TFS understand .user files and it would include it in version control? It's seems that is not the case.

Once I get TFS (takes a while in some places) I will have to remember to change the settings.


Emil said...

typo I said would and should be "would not"

(...) .user files and it would not (...)

Phil said...

Hi Emil,

Thanks for your comment.

Looking at our TFS, it appears that some projects had .user files, and some not, so I guess it depends how you add the project to TFS. If you just add files from your file system it doesn't allow TFS to parse which files should be added and which not. I guess the lesson is always tick the 'Add to source control' box when creating a project!