I've been working with the Database Publishing Wizard and have found what I initially thought was a bug but have decided is a rather nasty "feature". Or am I missing something?
I have membership implemented in a live database. I took a backup and loaded it into a local database. I created a new table locally that is foreign-keyed off aspnet_roles, and added some data.
Using the Publishing Wizard for a partial deployment, I followed the steps which seem perfectly clear to script out ONLY the new table and its data. The screens provide a nice GUI for selecting specific database objects, and I carefully chose the one new table.
From good habits I looked at the script before deploying it. Itdrops and recreates aspnet_roles and aspnet_applications as well (and inserts the data from the development tables).
Presumably this is because of the foreign key relationships. But there's no need to do so. What if I or another developer had added in some roles to the live table in the meantime? This script would blow them away.
Am I missing an option with the Wizard that would script out only what is specified? I've tidied up the script (i.e. removed the undesirable sections) - but this seems to be a naughty wizard. Any thoughts?
What publishing wizard are you using for your database? It is a bit expensive but I believe Red Gate has the best db tools available. It contains separate data comparison and sql structure (table changes) tools.
|||We use Red Gate when we have a choice. But one of our clients uses the Database Publishing Wizard, which is a free download from Microsoft.
I was curious so was checking it out. It seems to me that it should pop up a list of tables affected by the user choice, particularly when the user has selected only one table. Anyway, I'll continue pursuing this elsewhere, thanks for the response.
|||If that's the case is it possible to just modify the sql by removing the data insert statements?
|||At the risk of stating the obvious, did you try changing the "Drop existing objects in script" option to false? This will prevent the script from adding the if exists-drop statements when publsihing and when writing scripts.
This option is on the "Select Publishing Options" page.
|||
Yes, but take this scenario - I deploy a single new table fk-ed to aspnet_roles and uncheck "drop existing objects". Everything is hunky-dory first time round. But then I want to add a single new column to this recent table? It makes sense to check "drop existing objects" and then select the changed table - behind the scenes, aspnet_roles is dropped and recreated alongside the new table.
And regardless of whether the "drop existing objects" is unchecked - the Wizard will still try to insert the data from the development database into aspnet_roles. This will fail, because it turns identity insert on - so will bomb out on the primary key.
I seem to remember looking at the Wizard when it first was released and it didn't have partial deployment options - only full deployment. So partial deployment has been added later - which is a nice feature. But I think if the Wizard "decides" that it knows best and will also work with foreign-keyed tables for a chosen table in the GUI - it should warn the user of exactly which tables are affected.
Yes - the user can edit the script - but this Wizard isn't aimed at DBAs who will tend to want to roll their own scripts anyway - I think it's aimed at developers who are proficient in coding but don't want to get bogged down in the database side.
No comments:
Post a Comment