I'm using SMO's Transfer class to copy a production database to a test environment. That has worked splendidly until the production database got bigger and changed more frequently. Now, the copy operation often fails on getting the constraints up (I assume that's what it is):
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint <...>
That's odd, because I was assuming that the retrieval side of the copy operation was scoped in a transaction, and SQL Azure's default transaction level is read committed snapshot. A snapshot should show no constraint violations I would have thought.
I already tried putting the transfer's connection in another transaction, but that has no effect.
Does somebody know something about this?
I assume by local copy you mean database copy on your machine? At this point we do not recommend using SMO for that scenario. If source and target are both SQL DB the right approach is using CREATE DATABASE ... AS COPY OF (which you already do). If source or target is on-premise the recommended approach is to use classes in the Microsoft.SqlServer.Dac namespace as documented here.
Re SMO in general, a partial set of SMO is enabled only to provide Management Studio access to SQL Database. These objects offer limited functionality and are not intended for use in applications. The article on this was archived and we are following up to re-publish it.