Data dude erroring on deployment over-writing database file settings

At MITIE we've started using Visual Studio (VS) 2010 for managing database development. The nice thing about 2010 is that the functionality from Data Dude that used to be in a seperate sku and license is now included in the vanilla editions of VS Pro and Premium (Kimberly Tripp has a good post about it here). Premium has more functionality than Pro, obviously.

I won't cover the advantages of using VS 2010 to develop databases as that is covered in detail elsewhere. However there are some wrinkles that aren't quite so well documented.

I started to get this error whilst trying to deploy a database project from VS 2010, that I'd previously imported from SQL Server:

Error SQL01268: .Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file [file name] cannot be overwritten. It is being used by database [database name].

There is some information on the problem here however the thread is a bit long-winded and the final fix leaves something to be desired...

Essentially my problem boiled down to the fact that the database that I imported had different file settings to the database I was deploying to. In this case the filegrowth settings. Therefore the database project also had different filegrowth settings than the deployment database. In this situation VS/data dude will detect a difference and attempt to alter the database file. Obviously if there are open connections to the database then this won't work. Therefore there are two solutions:

1. Close all connections to the database and then deploy (possibly even putting the database in single user mode). This is a bit inconvenient and assumes you're happy with the settings in the database project. In this case I wasn't.

2. Happily there is an alternative. You can change filegrowth (and other) settings of the database project, however these aren't available through the VS 2010 IDE. Instead you have to edit .sql files found here:

[Project location]\[Database project name]\Schema Objects\Storage\Files

In my case I changed the filegrowth settings from:

FILEGROWTH=1MB

To:

FILEGROWTH=10%

After doing this my deployment problems were solved.

Clearly appropriate filegrowth settings is a whole seperate topic - which could be the subject of at a whole post in itself. On this occasion 10% was better than 1MB.

Unfortunately there is a limited set of database settings available to change through VS 2010 using the database properties > schema settings dialogue - which doesn't include autogrowth let alone sizing. Ideally it'd be nice to be able to amend those settings through VS itself. I think there's probably another Connect request in the works...