We have a production SQL Server database that's several years old at this point. When we need to add new objects, we add SQL scripts to this proprietary legacy process.

I've been asked to investigate doing database migrations via DACPAC. So I've set up a project and am converting some of the scripts we've added for an upcoming release over to this project format.

The problem I'm running into is that one of the scripts creates a table with a foreign key reference to an existing table. The project will not build due to error SQL71501, an unresolved reference to the existing table.

Am I supposed to add scripts to create my existing tables to this damn thing? Because I'm not thrilled by that prospect. Note that this is an error, not a warning, so I can't just suppress it at the file or project level. And just throwing it into a post-deployment script seems like it's missing the point.

7/25/2017 2:18:54 PM

Typically you do an extract on a pre-existing database to bring it out to dacpac, then go from there.

What tool are you using? Visual Studio?

7/25/2017 5:21:36 PM

Yeah, Visual Studio. I really wasn't wanting to extract the whole schema.

7/25/2017 7:52:31 PM

You could create a separate project, generate a dacpac from the current schema,and then use that as a reference in your upgrade VS project, then continue working toward a separate dacpac with just the upgrades/changes.

That's not super-maintainable long term, but I've done that in a pinch.

7/25/2017 8:29:10 PM

^ that's the best approach imo if you have to use DACPAC for migrations

7/25/2017 9:03:50 PM

Can someone explain why use a dacpac instead of a bacpac for migrations? Is that if you just want the schema and not the schema + data?

7/25/2017 9:30:38 PM

bingo

7/25/2017 10:08:45 PM

Well, I think this has put me off the idea, then. That's unfortunate.

7/26/2017 5:55:58 AM

does that mean you're looking for an alternative schema migration solution?

7/26/2017 9:47:25 AM

Well, I'm certainly open to hearing about alternatives.

7/26/2017 9:54:04 AM

I've used both https://github.com/fluentmigrator/fluentmigrator and https://dbup.github.io/ with good results. much more simple, focused solutions that yielding that DAC and SSDT sledgehammer.

7/26/2017 11:03:32 AM

Fluent Migrator wouldn't be a good fit, because I can't expect the other developers to learn a new dialect for this. Our current process basically just requires them to know how to write the SQL they need, and that's a stretch.

7/27/2017 10:10:08 AM

Fluent Migrator wouldn't be a good fit, because I can't expect the other developers to learn a new dialect for this. Our current process basically just requires them to know how to write the SQL they need, and that's a stretch.

DbUp doesn't look much different from our current process.

7/27/2017 10:14:13 AM