Data migrations between database versions

Is there a preferred way to handle data migrations between versions of databases using SSDT?

We have a large database project developed in SSDT, using post deployment scripts to insert/merge data to the database.

Now we're facing issues handling updates. We can't use merge scripts to update data because in some cases we need to manipulate the already existing data and therefore must implement custom update scripts.

I've looked into a solution using dacpac version number and verify which update scripts to run, but I realize that this solution has drawbacks.

What I have so far (pseudo code):

CREATE Procedure [dbo].[#GetDatabaseVersion](
@DbVersion NVARCHAR(64) OUTPUT)
AS
BEGIN
    select @DbVersion = type_version from msdb.dbo.sysdac_instances_internal where     instance_name = '$(DatabaseName)'
    RETURN
END


DECLARE @DbVersion NVARCHAR(64)
EXEC #GetDatabaseVersion @DbVersion = @DbVersion OUTPUT

IF @DbVersion = '2.4.0.0'
BEGIN
  --Do some data manipulation
END

IF @DbVersion = '2.5.0.0'
BEGIN
  --Do some more data manipulation
END

The drawbacks with the above solution is that I can't use GO statements in the IF-statements, and some of our code might be needing running the scripts in statement blocks.

Also is there any way to get the "DacVersion" from the SSDT project as an command variable?

链接地址: http://www.djcxy.com/p/62328.html

上一篇: dacpac可以用于管理数据量大的数据库吗？

下一篇: 数据库版本之间的数据迁移