Database Project - Controlling what gets included in the generated deployment script

Hello,

Question 1:
The deployment script generated out of the database project contains CREATE DATABASE script incase the database does not already exist in the target server.

But our requirement is in such a way that we want to control where our installer puts the DATA and LOG file in end user's machine.

Also we want to set the size of the new database as 3072KB instead of 2048KB, in addition to setting certain database options as ON or OFF.

So due to this same reason, we want to have control on CREATE DATABASE script, and do not want the database project to script out the CREATE DATABASE script in the generated deployment script.

Is there a way to suppress it?

 

Question 2:
Also, the database project will be used to deliver the database changes to the development team, so that they can simply select deploy on the database project to apply all the changes
to their local database.
The generated deployment script will also be used for production deployment.

For development team, I want the database project to do the following:
- Drop the database
- Create all the objects (tables, views, etc).
- Drop and Create views
- Insert all Reference/Lookup Data (since the database is being dropped every time)
- Insert Sample data
- Create user
- Set default database to the users created in above step.

 

For production environment, I want the deployment script to have:
- Create  DB only if it doe snot already exist.
- Create all the objects the very first time, and apply only delta changes during the subsequent product releases.
- Drop and Create Views during each release
- Insert all the lookup values the very first time, and later only apply updates to already existing lookup values (or) insert newly added lookup values in the subsequent release.
- No need to insert sample data
- Create user only when those users does not already exist in the end-user's database.
- Set default database to the users created in the above step.

 

Currently I have a sqlcmdvar called $(Environment) and set that to 'DEV' for development team mode so that all the items listed above under development team gets executed when deployment is done from DB project.

The problem is, those items that is supposed to get executed only in DEV also gets scripted in the final generated deployment script which will also be used for production deployment.

So the insertion of sample data gets scripted in the generated deployment script, although I do not want that portion of code for production.

I tried by adding IF statement around code so that only certain portion gets executed for PROD. But irrespective of that, everything gets scripted in the generated deployment script although the IF statement do exist around the code.

I'm wondering if there is a way to tell VS2010 DB Project to script only those items that are applicable for PROD environment in the final deployment script, instead of everything mentioned in the database project.


Question 3:
Since we are dropping the database everytime in DEV, and do not drop database at all in production environment if already exist, getting the DROP DATABASE statement scripted out in the final generated deployment script is a bit scarey even though I have an IF statement around that DROP statement to check for $Environment as 'DEV' as mentioned below.

Is there a way to make the databse project script DROP DATABASE the database everytime only when the $(Environment) sqlcmdvar is set to DEV, but not script the DROP DATABASE statement to the final generated deployment script?
Or in other words, is there  way to make the database project script the DROP DATABASE statament in final generated deployment script only when the $(Environment) sqlcmdvar is set to DEV and not when $(Environment) sqlcmdvar is set to PROD.

 
 

USE [master]

IF '$(Environment)' = 'DEV' 
 BEGIN
  IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TEST_DB') 
   BEGIN 
    ALTER DATABASE TEST_DB SET single_user with rollback immediate
    DROP DATABASE TEST_DB;
   END 
 END
 

IF NOT EXISTS (SELECT name FROM sysdatabases WHERE name = N'TEST_DB')
BEGIN


 IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'TempDBForDefaultPath')   

 BEGIN  
  DROP DATABASE TempDBForDefaultPath  
 END; 

 
 CREATE DATABASE TempDBForDefaultPath; 

 DECLARE @Default_Data_Path VARCHAR(512),   
   @Default_Log_Path VARCHAR(512),
   @data_file_location nvarchar(260),
   @log_file_location nvarchar(260),
   @CreateDBSQL nvarchar(2000);


 --Get the default data path   
 SELECT @Default_Data_Path =    
 (   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) 
  FROM sys.master_files mf   
  INNER JOIN sys.[databases] d   
  ON mf.[database_id] = d.[database_id]   
  WHERE d.[name] = 'TempDBForDefaultPath' AND type = 0); 


 --Get the default Log path   
 SELECT @Default_Log_Path =    
 (   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)   
  FROM sys.master_files mf   
  INNER JOIN sys.[databases] d   
  ON mf.[database_id] = d.[database_id]   
  WHERE d.[name] = 'TempDBForDefaultPath' AND type = 1); 


 --Drop the temp database 'TempDBForDefaultPath'
 IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'TempDBForDefaultPath')   
 BEGIN  
  DROP DATABASE TempDBForDefaultPath  
 END; 


 SET @data_file_location = @Default_Data_Path + N'TEST_DB.mdf'; 
 SET @log_file_location  = @Default_Log_Path  + N'TEST_DB_log.ldf'; 
  
 
 --Create TEST_DB Database
 SET @CreateDBSQL = 'CREATE DATABASE [TEST_DB] ON  PRIMARY ( NAME = N''TEST_DB'', FILENAME = ''' + @data_file_location + ''', SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
      LOG ON 
      ( NAME = N''TEST_DB_log'', FILENAME = ''' + @log_file_location + ''' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)';

 EXEC(@CreateDBSQL);

END

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TEST_DB].[dbo].[sp_fulltext_database] @action = 'enable'
end

ALTER DATABASE [TEST_DB] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [TEST_DB] SET ANSI_NULLS OFF

ALTER DATABASE [TEST_DB] SET ANSI_PADDING OFF

ALTER DATABASE [TEST_DB] SET ANSI_WARNINGS OFF

ALTER DATABASE [TEST_DB] SET ARITHABORT OFF

ALTER DATABASE [TEST_DB] SET AUTO_CLOSE OFF

Open in new window



Thanks!
sath350163Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>Is there a way to suppress it?<<
You can certainly script the CREATE DATABASE T-SQL command to include the path of the Data and Transaction Log file as well as the initial size.  Is that what you mean?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.