To improve the DX and address problems with migration conflicts during development we want to move from a common dev DB to a local copy on each developers machine.
My idea is to provide a tool or script that will create a backup of some sort and copy it to the local dev machine, where it can be applied to a docker hosted instance.
I managed to get it working using a data-tier application for most databases but the problem is that some databases have schemas authenticated against users that cannot be created on the users machine because they are using Windows authentication in prod.
I don't need the users copied over and would be fine having everything under the SA user. I am only interested in tables, schemas, functions, stored procedures and views.
The developers use a MacOS machine so they cannot use SSMS. Also the prod databases file system is not accessible by the devs.
You can use sqlpackage.exe to extract a dacpac that excludes server scoped objects such as logins and credentials.
sqlpackage.exe /a:Extract /p:ExtractReferencedServerScopedElements=false /p:ExtractAllTableData=true ...
Note the use of /a:Extract instead of /a:Export to generate a .dacpac instead of a .bacpac
If you need additional flexibility, since .dacpac's and .bacpacs are just zip files, you can unzip/edit/rezip them to perform any additional customizations required.