Search code examples
sql-servervisual-studio-2015sqlcompare

How to detect TFS database CLR assembly file while comparing Scripts folder from TFS to Database in SQL Compare

I am trying to create a database from my TFS project using SQL Compare.

Currently we compare our Database project to a CI database using the Compare tool in Visual Studio however, I have been asked to make this eventually run as a build step as part of the automated deployment.

Problem* SQL Compare will not recognize the database assembly file generated within visual studio. Therefore some computed columns that use clr functions fail on deployment.

I had success using SQL Compare to script a previously scripted Database to a 'Scripts Folder'. Next I pointed my SQL Compare left side to that new 'Scripts Folder' and right side at a new Database and the Assembly file was detected on the left side.

I have then tried copying the database assembly file to the visual studio database project location with no luck.

I unpacked the generated file 'Database.xxx.dacpac' file to a folder and set my Left side compare in SQL Compare to that. SQL Compare complains about "Scripts folder found with missing metadata file". There were other errors after I hit 'Compare' but I ignored them. However my assembly file showed up! Is this supposed to be the correct process? assuming I remove all the warnings?

I expect to be able to compare my database project file in visual studio to a fresh database and update it within SQL Compare

Solution
    1. Right click on your Database project file in the Solution Explorer
    2. Go to build - and look up the location of 'Build Output Path' find the 'database.dacpac' file

    Option A: double click and unpack the .dacpac file. In SQL Compare use option 'Script Folder' and use that newly created folder as a source, you can then create a database from your visual studio database project. SQL Compare - using DACPAC file

    Option B: Use command line and SSMS application sqlpackage.exe to create database directly from the dacpac file without extracting.DACPAC Deployment using command line

    Option C: Right click on database you want to upgrade in SSMS - and click on Tasks->'Upgrade Data-tier Application'. Use the DACPAC file as the source.