How to avoid the error "The file 'xyz.mdf' cannot be overwritten. when deploying

Somehow when generating a script via deployment on an existing database, a statement to add a mdf file is generated.

:setvar DatabaseName "nameofdatabase"

:setvar DefaultDataPath "c:\apath\"

ALTER DATABASE [$(DatabaseName)]

ADD FILE (NAME = [DifferentNameThanDatabase], FILENAME ='$(DefaultDataPath)$(DatabaseName).mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TOFILEGROUP [PRIMARY];

Both the database and the file already exists.  However the file name (Name = ...) does not correspond to the databasename (setvar DatabaseName ...).

In the project, the file is defined like this:

ALTER DATABASE [$(DatabaseName)]

ADD FILE (NAME = [DifferentNameThanDatabase], FILENAME ='$(DefaultDataPath)$(DatabaseName).mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TOFILEGROUP [PRIMARY];

Is there a work around that?  I have to deploy the same model to multiple database on the same server.

I believe I found the issue.

I hope I am the first one, I can at last contribute!

In the definition of the file, there is one part which is static.  This seems to be the source of the issue.

ADD FILE (NAME = [DifferentNameThanDatabase], FILENAME ='$(DefaultDataPath)$(DatabaseName).mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TOFILEGROUP [PRIMARY];

the name part (NAME = [DifferentNameThanDatabase]) is static and not affected by the sqlcmdvars.  In SSMS, this corresponds to the logical name of the file.

As such, when attempting to deploy to a database, the $(DefaultDataPath) and $(DatabaseName)variables adapt to the database I wish to update but not the logical name.  In my specific case, the logical name of the of the file was different but the concatenation of '$(DefaultDataPath)$(DatabaseName).mdf' did correspond to the file of the database to update.

I believe what happens is the following:  due to the different logical name, no match is found.  As such vstsdb attempts to create the file with the phisical path and name of the file already in use. hence the error: "The file 'xyz.mdf' cannot be overwritten"

Changing the logical name of the file (static part of the file definition) before deployment seems to fix the issue.

Hey bccollin,  you are right it is quite an hassle.  Still it is the only workaround I was able to work it out.  The same bug seems to be present in 2010 or 2008r2  (not sure of the name and don't have it installed anymore).

That said, there is a work around to make it automated.  You can edit the dbproj file to add an event before the build.  In this event, you can call a sqlcmd to update the file names.

Or if you don't have the time to invest in this solution, you can simply make all your file names the same (data, log).  It does not involve any interruption on production.

Here is the TSQL code to change the database file names.

declare @filename sysname,@alterstatement varchar(max)select @filename = name from [$(dbname)].sys.database_files where type = 0 and name <> 'Data'if @@rowcount = 1beginset @alterstatement = 'use masteralter database $(dbname)MODIFY FILE(NAME = ' + @filename + ',newname = ''Data'')'print 'Prebuild: Renaming Data file'exec (@alterstatement)end

If you want to update update all database, you can simply loop on master.sys.databases and run the statement for all.  Do the same for the logs (filetype = 1).

If you go this way, you may have to run the script regularly by putting it in a job.  New databases take the name of the database for the filename by default.

There are plenty of posts about this but they all contain the same erroneous fix to put a variable in the name part of the file which does not work.

I believe I posted a bug on connect about this.  Feel free to add to it or to post your own.  I would also like to see this bug taken seriously.

Until then, I don't think you will get any better than this answer.  If you do, please post it here, I would be happy to learn about it.

Good luck.

Hi Barclay,  The issue seems to be something else.

The only variable which needs to be set in sqlcmdvars is the path1 which is used for the log files.
The DefaultDataPath and DatabaseName are set at deployment time and are the variable used for the mdf file.

Currently my issue is with the mdf file which cannot be set in the sqlcmdvars.  

As for the deployment properties, the only information I can set which seems related arethe target database name and the sqlcmdvars which are connected to the adequate information for the configuration I attempt to deploy.