What causes "SQL01268: Msg 1834: cannot be overwritten. It is being used by database"? (in Database Project)

Question

Full error below:

Error 1 SQL01268: .Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\testdatabase.mdf' cannot be overwritten. It is being used by database 'testdatabase'. SchemaCompare5 25 0

I read about this on some forums and quite a few people were getting this and supposedly for some it had to do with parameterising the file path name to the db etc. or ticking "ignore file names and path for files and log files" prior to doing the comparison - this I have tried to no avail.

Someone else who has the same/similar issue: http://social.msdn.microsoft.com/Forums/en/vstsdb/thread/5a8b8c52-adb4-4a5a-95ed-09ad22bacf60

Basically for me I seem to get this error irrespective of which databases I am using for target and source. Say even if I create a new database with one table and another database with no tables and different name and try to update the schema of the database with no tables using the db with the single table it still gives me the error. Almost like SQL server express has gone nuts. I remember using the schema comparison tool before with no trouble. All db connections were created, tried many ways to do this to no avail ie: pointing to copy of *.mdf db in another folder or deleting things from the DATA folder in mysql directory in program files etc.

Also believe I read someone had solved a similar issue be deleting some files the scheme comparison tool creates, think they were *.sql type not sure which ones though.

Answer 1

The problem arises because the database files already exist.

Try the below within the Visual Studio database project.

Create the schema comparison.

Go to menu: Data > Schema Compare > Export to > Editor

Once the script has been created delete the alter database commands that add the physical files. Then create a connection, switch to SQLCMD mode (making sure you have focus on the script) and execute the script.

To switch to SQLCMD mode access: Data > Transact-SQL Editor > SQLCMD Mode