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
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.