Avoid manipulation of mdf file using msbuild

Question

I have a project which I try to deploy using msbuild to a database and I just keep getting blocked by the following message:

xyz.dbschema : Deploy error TSD01268: .Net SqlClient Data Provider: Msg 1
834, Level 16, State 1, Line 1 The file 'E:\MSSQL\Data\xyz.mdf' cannot be overwritten.  It is being used by database 'xyz'.

The issue is the database using the file is the the same database on which I attempt deploy and the file it attempts to add is a file which already exists under the same specification as defined in the project.

I tried many solutions but none seems to work:
1 - Make the file definition identical in the project as in the database (logical name, physical path, size, growth, maxsize).  Schema compare displays the files as being identical but msbuild insists to create the already existing file.
2 - use the file Database.sqldeployment and specify to ignore fileandlogfileplacement, filegroupplacement, filesize, fillfactor.  This does not seem to have an impact.
3 - remove the definition of the files in the project and set the option not to drop objects not defined in the project.  msbuild seems to make its own definition and attempt to deploy them.
I also tried leaving the file in the project but commenting them out.  I got the same effect as #3, msbuild seems to create it's own definition for the files if none are specified.

Is there a way to deploy to a database using msbuild?

p.s. I know there are countless post on this subject but most have been set as answered by moderators instead of the posts creator with no confirmation that it actually works.  The method of deployment is also rarely specified (msbuild, vsdbcmd, schema compare)

I am using msbuild.

Answer 1

I'm assuming you are using the GDR R2 version. Here's what I do/did succesfully:

1. Create a new project
2. Use Import Schema to import AdventureWorks2008 into the project
3. Modify the FileStreamDocuments definition from

ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [FileStreamDocuments], FILENAME = '$(DefaultDataPath)Documents') TO FILEGROUP [DocumentFileStreamGroup];

to

ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [FileStreamDocuments], FILENAME = '$(DefaultDataPath)$(DatabaseName)_Documents') TO FILEGROUP [DocumentFileStreamGroup];

4. In Project Properties I set the deploy settings:
4.1 Deploy action - Create a deployment script and deploy the database
4.2 Target connection - my SQL instance
4.3 Target database name - dukek-foo

5. Save the solution
6. From a command shell
6.1 Change directories to the location of the project file
6.2 msbuild /t:build
6.3 msbuild /t:deploy

This succesfully creates a new database named 'dukek-foo' and the MDF file dukek-foo.mdf.

7. Go back to the project and add a table (or some other change)

8. From a command shell
8.1 Change directories to the location of the project file
8.2 msbuild /t:build
8.3 msbuild /t:deploy

This successfully adds the new table to the existing database dukek-foo (i.e. dukek-foo.mdf)

---

Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)

Answer 2

Thanks Duke.

I seem to be getting into another problem.  The installation instruction for gdr2 (9.1.40413.00) do not seem to work.

Though I see gdr in my version:
Microsoft Visual Studio Team System 2008 Database Edition GDR Version 9.1.40320.00

the statement to uninstall it does not seem to work:

http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en
Uninstall any previous version of the GDR for Database Edition. To uninstall a previous version of the GDR from the command line, use: msiexec /X {DDF197C6-4507-3A19-A4B5-0E17CC931370}

I just keep receiving the message: "this statement is only valid for applications currently installed"

Of course, when attempting to install gdr2, it refuses and gives the following message:

Visual Studio Team System 2008 Database Edition GDR does not apply, or is blocked by another condition on your system. Please click the link below for more details.

Answer 3

Though I succeeded in upgrading my gdr version and performing the test, I still get the same issue.

Similarly to the test listed, I imported a database and exported it.  It worked just fine.
Then I took the file definition or the new imported db and copied them to an existing project.  then I tried to export it to the same databse from which the import was done.  I still get the same error.

It might be due to the options defined in the sqldeployment.  However, is there known issues a project created under the first version gdr is deployed using gdr r2?

Answer 4

Got it.

Added the option to avoid the file in the sqldeployment file.
Changed the path of the log files.
successfully moved to gdr r2.
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/dfe48961-0a6e-49ef-ac9f-ca4213455f0f

Not sure if all that was needed or if some of those changes would have sufficed but it worked.

This also means that the fact that the project was created before gdr2 does not have an impact in my case.