Update existing database using latest dbschema and vsdbcmd tries to alter mdf

Question

When I try to do a vsdbcmd deploy using my latest dbschema, the resulting script tries to alter the mdf file, which results in the sql failing because the database is in use.  Is there a way to avoid the alter mdf statement from generating in the deployment script?  

This alter is appearing I feel because the $DatabaseName references in the dbschema file doesn't match the statically named file on the server, so it tries to alter it to the $DatabaseName

This is in GDR2

Answer 1

So in the script for your File, what variables are you using for the Filename. Normally when you import, the path and the database name will be replaced with something like 

'$(DefaultDataPath)$(DatabaseName).mdf'

 

 

. $DatabaseName and $DefaultDataPath are replaced at deployment with values from the target server. Now, if your file doesnt use these values, you coule either create new sqlcmd vars for it, or just put in the static path.

I hope I understand your problem correctly. Let me know if this helped.

Christian.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

I'm seeing a similar problem, and when I look in the deployment script I'm not getting the $(DefaultDataPath)$(DatabaseName).mdf - I'm getting hard-coded filepaths.  This is true even when I set the "Ignore file names and paths for files and log files" option in the Schema Compare Options.  I'm not sure if that's what this option is for - I've been trying to find documentation or some reference to the options.  At this point I'm at a stand still and can't specify a deployment database.

Any help with this woudl be greatly appreciated.

---

-DAD

Answer 3

On import, if the engine detects that your file was not store in the default location, or the file is not named the same as the database, it will not substitute the variables; However, you can still go to your file script, and put these values in.

The "Ignore file names and paths for files and log files" option should prevent deploying your files if the file name differs from the project to the database. If there is something else different about the file, it will still update it. You can see what differences it is finding if you do a schema compare, and then drill into the files results. It should give you a list of the properties.

If you have more questions or concerns, or I haven't solved your problem, I would be happy to help futher.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

Thanks Christian - this is helpful.

I found that if I created the project using the Wizard, as opposed to just selecting a DB project, the import process as part of the wizard appeared to use the variable substitution.

In the end the point is moot for myself, at least for now, as we decided to back out the GDR for the time being.  We have a rather large project and not everyone is ready to move to the GDR just yet. 

Thanks for your help.

---

-DAD

Answer 5

In my case we are specifying the name as you indicated...

'$(DefaultDataPath)$(DatabaseName).mdf'

We need this to be like this in our db project so we can replace it depending on which server for which client we are deploying to, however, the dbschema, with this in it, sees it as different as the static paths when comparing to a deployed db, and therefore tries to update the mdf to the same name it already has, resulting in the object in use errors.

Answer 6

Frickg:

Something dosent sound right. When you do a schema compare, what does it see as the diferences between the file groups? If it is infact, just the file path, then we may have stumbled onto a bug.

One way that you could get around this for the moment would be to do your deploy through schema compare. It would allow you to ignore the filegroups and generate an update script for your other elements.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 7

Frickg:

Something dosent sound right. When you do a schema compare, what does it see as the diferences between the file groups? If it is infact, just the file path, then we may have stumbled onto a bug.

One way that you could get around this for the moment would be to do your deploy through schema compare. It would allow you to ignore the filegroups and generate an update script for your other elements.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Let me recount the steps we are doing, the exact error and what the problem is to be a little more clear:

1.  We generate a .dbschema from our db project using MSBuild, note, our project uses $DataDefaultPath and $DatabaseName for the file, which results in the dbschema having those in the xml.

2.  We generate the .sql deployment script from this .dbschema using vsdbcmd with the /a:Deploy and /p:AlwaysCreateNewDatabase=true switches.

3.  We run this .sql to create the database on our server.

4.  We drop all constraints/indexes/keys/etc. from this database.

5.  We run a series of SSIS ETLs to populate this database (dropping constraints reduces logging and improves performance drastically)

6.  We run step 2 with AlwaysCreateNewDatabase=false using the original .dbschema against the database on the server, so it creates a differential script that will recreate constraints/indexes/keys/etc. 

7.  We run this .sql file against the server and get this error:

     [exec] The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDb.mdf' cannot be overw

ritten.  It is being used by database 'MyDb'.

     [exec] Msg 1828, Level 16, State 4, Server MyComputerName, Line 1

     [exec] The logical file name "MyDb" is already in use. Choose a different name.

This is because the script generated in step 6 has this line in it:

GO

ALTER DATABASE [$(DatabaseName)]

    ADD FILE (NAME = [$(DatabaseName)], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', SIZE = 100000 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 100000 KB) TO FILEGROUP [PRIMARY];

This line is generated because the vsdbcmd is not realizing the update script it is generating is for the same database as created in step 3. 

We hacked around this by doing a find/replace of $DatabaseName in the .dbschema file with 'MyDb' before running step 6 so that the .sql generated in step 6 doesn't include the alter mdf statement.

There is no reason the alter database statement should be there, I am saying 'generate me a differential script verse MyDb' so it should KNOW the database name and not try to alter it.  

We also tried in step 6 passing the same database name via the /DatabaseName switches and it didn't matter.

Christian, 

We also tried step 6 via the visual studio schema compare and it STILL generates the alter statement so this definitely seems like a bug.

Any thoughts?

Answer 8

Frickg:

I tried your steps, and I cant get it to repro. It could be caused by something in your database. Would it be possible to get a copy of your schema to try to repro the issue with?

Christian.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 9

I cannot share our database with you, let me see if we can squeeze in the time to rig up a bogus version doing the same thing.

Can you share your version?

Also, are your files and file groups driven by files?  We files and filegroups configured as scripts in the db project.

Answer 10

Hey Greg,

I believe my answer to the linked thread addresses your issue in deployment.  Basically, in the end, you can not use variables in the logical names of your File\LogFile objects.

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/beaae56e-234b-4216-bcf5-e8c2b8bf25e2/

Andrew

Answer 11

Hey Greg,

I believe my answer to the linked thread addresses your issue in deployment.  Basically, in the end, you can not use variables in the logical names of your File\LogFile objects.

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/beaae56e-234b-4216-bcf5-e8c2b8bf25e2/

Andrew

Ugh.  Well how about this issue (I can post separately but it is a similar issue)

I added partitioning to my database today, on 3 tables.  EVERYTHING started blowing up.

1.  I have a table, which is now partitioned (On MyPartition(partitioningfield) is at the end of the create table script).

2.  That table has an index.

3.  I deploy the db. When it deploys, everything is fine.

4.  We drop the constraints, run our ETL.

5.  As described, then when we create the differential script against this database.

6.  Now the problem is this script does the following:

a.  Creates xx_tmp_MyTable without partitioning, but with the identity constrating

b.  Moves MyTables data to xx_tmp_MyTable

c.  Drops MyTable

d.  It Renames the xx_tmp_MyTable to the original name MyTable.

e.  It generates the partitioning function and scheme

f.   Repeats steps a-d but this time WITH my partitioning on the table but this time also creates the index and renames it when renaming back to MyTable

g.  Trys to readd the index on MyTable which failed because the script that added partitioning back on adds the index.

This screams of a bug in the tool.  Partitioning was NEVER dropped off my db in step 4, but because the tool first creates the table with the identity/pk etc on it, it generates script to readd partitioning, and I think it must inject this script and it renames the index on the xx_tmp_MyTable to the real name, causing the later index to blow up.

The partition adds this index, and I don't think it keeps track internally so later on it already thinks it has to add the index, which bombs due to the index already existing.  

Can you confirm this is an issue?