Database object with Drop, Create, Grant(s) in one file

Question

Hello, in Visual Studio (VS - dbpro) when a database schema is imported into a database project, the database objects are scripted to CREATE (eg CREATE PROCEDURE xxxx...).  To make a change to the stored procedure the developer has basically two options:-

(a) make the change to the file, save, perform a db diff, then run the script into the SQL Server instance

(b) copy/paste the script into SSMS (SQL Server Management Studio), make the change, replace CREATE with ALTER, execute, then copy/paste the script back into VS, replace ALTER with CREATE, then save

Both options may need a few iterations as the fix might not be perfect the first time (which we all know is more often than not) - and both are cumbersome

I wondered whether anyone had been able to automate the process to include in each database object definition file (one file per database object) everything to teardown and recreate the object - that is, the single file would include DROP, CREATE, and all associated GRANTs for that object.  I could simply double click on the file, edit it, execute it against the SQL Server instance, then save and checkin - all within VS - perfect.  (I realise that come db diff time the definition text may flag differences because the comparer may expect simply the CREATE (as opposed the DROP, CREATE, GRANT), but I was also hoping there would be a solution/work-around for this also)

I could go thru each database object and copy/create the DROP and GRANTs manually into the file that contains the CREATE however this is very manual.

Does anyone have any idea how this might be automated - is there a configuration item that could be leveraged - etc

Many thanks

Travis

Answer 1

This maybe obvious but can't you "Deploy" the database? That will give you a script that contains all the changes needed to modify the target database to make it the same as the one in Visual Studio?

Deploy can be found of the Build menu. You need to configure the Deploy settings by right-clicking on the project and selecting "Properties" then going to the Deploy tab. You can set up the connection string and such like in there.

Once that's done Visual Studio will give you the script you want for free.

Development in Visual Studio always uses "CREATE" statements for this reason. We rely then on Visual Studio to understand the changes we have made and build an appropriate "change script" or "Deployment Script" to apply the changes to the database.

In the settings window I mentioned above, you can also get Visual Studio to automatically execute the script so that changes are made straight away with no copy-paste in SSMS required.

HTH

Answer 2

Thanks HTH, I understand what your saying, but I need to be more granular than the entire Project when Deploying to the SQL instance (the process you outline above is at the Project level)

I don't mind building the project after a change but the push into the instance needs to be a single object at a time as I am making the change.  I feel much more comfortable Deploying individual database objects especially when the number of objects in the databases are say around the 500-1000 mark and the number of developers making-changes / checking-in could be somewhere between say 3-6.  I need to feel completely confident that the item I have just modified is the only one to be deployed to the instance - and I don't really want to compare, because that will take too long for small changes.  I also don't want to pick up the deployment script, maybe amend, and execute it outside the workflow

Is there a means to change and Deploy just one item seemlessly within VS ?

Answer 3

Hello Travis,

Once you specify the target database in the project properites, and at the same time do not select the Always re-create database option in the Database.sqldeployment file. When you deploy your database project, one deployment file is generated with an incremental diff script. Is that file meet your requirement?

I personally do not think there is a automatic way for you to fulfill your requirement you mentioned in your initial post. And if you would not like to use the deployment file, I think another choice for you is using schema compare (which you may not prefer). Using schema compare to get the modification script, and then write update to the target source database, and then check in your changes.

In addition, as you are using TFS Version Control to store your source code, it is convient for you to get the history of one source controlled file. You can get the information who at what time check in what changes. So, it is OK for other to modify and check in files. If you still do not like others to work on these files, you can grant the DENY permissions for them in the TFS Source Control Explorer.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us