locked
How should database project deployment be done in our scenario?

    Question

  • Our scenario:
    We are developing an application that uses database. During developement we work against database1. Our test team is testing our application after each version of the application is deployed to a server for testing. The test version of the application uses database2. Recently we started looking at database edition of Visual Studio 2008 Team System, to avoid dataloss for the testers after each deployment.

    We started making a database project into source control, and imported the schema of database1. It was building successfully.
    During developement the database1 can also be changed outside the project directly in the database. So we might need to use schema compare between database1 and the project and update the project.

    During each deplyment of the application we want to update the schema of database2 without loosing any data there, that the test team have created before.

    What's the best practice for this?
    -Using schema compare between project and database2 and Write updates to database2
    -Changing the project properties under Deploy settings > Choosing database2 > Deploy

    I've tried both many times, but get problems with this.

    Using Deploy gives this output:

    ------ Deploy started: Project: NorTrim_Database, Configuration: Debug Any CPU ------

    NorTrim_Database.dbschema(0,0)Warning TSD01266: The object [Leif_NorTrim_2009] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    NorTrim_Database.dbschema(0,0)Warning TSD01266: The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    Deployment script generated to:

    C:\Visual Studio 2008\Projects\NorTrim_Database\sql\debug\NorTrim_Database.sql

    Processed 264 pages for database 'Leif_NorTrim_2009', file 'Leif_NorTrim_2009' on file 1.

    Processed 3 pages for database 'Leif_NorTrim_2009', file 'Leif_NorTrim_2009_log' on file 1.

    BACKUP DATABASE successfully processed 267 pages in 0.445 seconds (4.677 MB/sec).

    Creating Nortrim...

    NorTrim_Database.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'D:\MSSQL2008\Data\NorTrim_2009.mdf' cannot be overwritten. It is being used by database 'NorTrim_2009'.

    An error occurred while the batch was being executed.

    Done executing task "SqlDeployTask" -- FAILED.

    Done building target "DspDeploy" in project "NorTrim_Database.dbproj" -- FAILED.

    Done executing task "CallTarget" -- FAILED.

    Done building target "DBDeploy" in project "NorTrim_Database.dbproj" -- FAILED.

    Done building project "NorTrim_Database.dbproj" -- FAILED.

    Build FAILED.

    ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

    ========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========


    ------------------------------------------------
    It seems that its trying to change the mdf file on the database I imported from.

    Using schema compare gives this output:

    --------------------------------------

    The object [Leif_NorTrim_2009] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    Target database synchronization has started.

    Creating Nortrim...

    .Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'D:\MSSQL2008\Data\NorTrim_2009.mdf' cannot be overwritten. It is being used by database 'NorTrim_2009'.

    An error occurred while the batch was being executed.

    Update to target schema was canceled.

    The object [Leif_NorTrim_2009] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    The object [Leif_NorTrim_2009] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    .Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'D:\MSSQL2008\Data\NorTrim_2009.mdf' cannot be overwritten. It is being used by database 'NorTrim_2009'.

    The object [Leif_NorTrim_2009] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    The object [Leif_NorTrim_2009] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    .Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'D:\MSSQL2008\Data\NorTrim_2009.mdf' cannot be overwritten. It is being used by database 'NorTrim_2009'.

    ----------------------------------------

    It seems that its trying to change the mdf file on the database I imported from.

    How should this be done correctly???
    Tuesday, June 02, 2009 7:47 PM

Answers

  • Couple of things jump in to mind:

    • Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'D:\MSSQL2008\Data\NorTrim_2009.mdf' cannot be overwritten. It is being used by database 'NorTrim_2009'.
    • This is caused by the fact that you file definition is a literal and you are not using a parameterized definition like this:
      ALTER DATABASE [$(DatabaseName)]
          ADD FILE (NAME = [Northwind], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', SIZE = 11520 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];
      
      
    • The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    • This is caused because you have objects in the target database that are not part of the prject, by default we do not delete objects that are orphaned.

    Hope that helps,
    GertD @ www.DBProj.com

    • Marked as answer by leifosor Monday, June 08, 2009 7:43 AM
    Friday, June 05, 2009 10:27 PM

All replies

  • Couple of things jump in to mind:

    • Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'D:\MSSQL2008\Data\NorTrim_2009.mdf' cannot be overwritten. It is being used by database 'NorTrim_2009'.
    • This is caused by the fact that you file definition is a literal and you are not using a parameterized definition like this:
      ALTER DATABASE [$(DatabaseName)]
          ADD FILE (NAME = [Northwind], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', SIZE = 11520 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];
      
      
    • The object [Leif_NorTrim_2009_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    • This is caused because you have objects in the target database that are not part of the prject, by default we do not delete objects that are orphaned.

    Hope that helps,
    GertD @ www.DBProj.com

    • Marked as answer by leifosor Monday, June 08, 2009 7:43 AM
    Friday, June 05, 2009 10:27 PM
  • Incidentally, the overwritten error can also occur in a different cirucmstance.  If you change the logical filename in the script, but try to make it point at the same path, you'll get that error.

    This may seem intuitive and not an issue, but as it turns out we encountered this with several clients.  They had our original install, but changed the name of the logical file names after install ([Northwind] in the above example from Gert).  When VSDBCMD ran against that with our later patch, it didn't find the file and tried to create a new file at the same path as the old one.  Explosion :).  There's of course no way for the installer to know for sure that they had simply renamed the internal logical name, so we had to manually change their logical names back to the original values to get VSDBCMD to successfully execute.

    Adding to this thread for future reference by anyone that might face the same problem.

    Thanks,
    -Dale
    Thursday, July 23, 2009 10:16 PM
  • After a little digging, I found a way around this.  There are two files within the project structure that contain those database file statements.  They are:

     <project location>\Database\Schema Objects\Storage\Files\PrimaryFileName.sqlfile.sql

    and

    <project location>Database\Schema Objects\Storage\Files\PrimaryLogFileName.sqlfile.sql

    Because I never let the deployment process create databases, I just deleted the statements from these two files.  Looking back, I probably should have just commented them out.  I guess that would accomplish the same thing.  But, this allows the deployment process to continue.

    Hope this helps someone out.

    Greg

    Saturday, March 20, 2010 8:24 PM
  • We get the same warning from our deployments because the logical name of our files are parameterized along with the actual file name.  This prevents the file in the project from being compared to what's in the destination server so I wrapped the creation in a conditional.  I then suppressed the warning by unchecking the "UnmodifiableObjectWarnings" box.

    if not exists (select * from sys.master_files where database_id = DB_ID('$(DatabaseName)') and name = '$(DatabaseName)_LogAuto')
    begin
    		PRINT 'Creating Auto-Grow Logfile...'
    		ALTER DATABASE [$(DatabaseName)]
    		ADD LOG FILE (NAME = [$(DatabaseName)_LogAuto], FILENAME = '$(DefaultLogPath)$(DatabaseName)_logAuto.LDF', MAXSIZE = 2097152 MB, FILEGROWTH = 51200 KB);
    end

    Thursday, May 03, 2012 5:44 PM