Options
SQL Source Control 5 data-only migration script not syncing
CraigEddy
Posts: 36 Bronze 3
Created a "data only" migration script for the sole purpose of updating one record in a table (we're experimenting with using this to deploy DML scripts).
The script:
Using TFS Build vNext task to "sync" to a target (test) database. In the build logs I see the following (and in the database on the target machine, I don't find a RedGateLocal.DeploymentMetadata table). Note that the SQL file referenced is indeed the migration script's file:
2016-09-02T20:18:19.5026163Z Executing the powershell script: C:agent2 asksRedgateSqlCiBuild1.0.21SqlCiTask.ps1
2016-09-02T20:18:19.6432189Z Entering script SqlCiTask.ps1
2016-09-02T20:18:33.7882271Z sqlCI.exe -- Red Gate's SQL Continuous Integration v2.1.33.387
2016-09-02T20:18:33.7882271Z STARTING: Updating database
2016-09-02T20:18:33.7882271Z Starting: C:Program Files (x86)Red GateDLM Automation Suite 1SCSQLCompare.exe /include="Identical" /include="StaticData" /scripts1="C:Usersdeployer.saAppDataLocalTempSQL CIcszpz2yr.v55dbstate" /exclude="User,Queue,Role" /server2="STE_NGTESTDB02" /database2="FITDB" /synchronize /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseCompatibilityLevel,UseMigrationsV2,IgnoretSQLt"
2016-09-02T20:18:33.7882271Z SQL Release: activated, edition: standard, serial number: 610-001-266071-3B82
2016-09-02T20:18:33.7882271Z SQL Compare Command Line V11.6.11.2463
2016-09-02T20:18:33.7882271Z ==============================================================================
2016-09-02T20:18:33.7882271Z Copyright Copyright c 1999 - 2016 Redgate Software Ltd
2016-09-02T20:18:33.7882271Z Warning: Could not find the file 'RedGateDatabaseInfo.xml' in the scripts folder
2016-09-02T20:18:33.7882271Z 'C:Usersdeployer.saAppDataLocalTempSQL CIcszpz2yr.v55dbstate': the
2016-09-02T20:18:33.7882271Z default settings will be used instead.
2016-09-02T20:18:33.7882271Z Registering data sources
2016-09-02T20:18:33.7882271Z Warning: Ignored statement found in file
2016-09-02T20:18:33.8040974Z C:Usersdeployer.saAppDataLocalTempSQL
2016-09-02T20:18:33.8040974Z CIcszpz2yr.v55dbstateDatabaseCustom Scripts2016-09-02-151557 hd
2016-09-02T20:18:33.8040974Z userMigrationScript.sql at line 6
2016-09-02T20:18:33.8040974Z Creating mappings
2016-09-02T20:18:33.8040974Z Comparing
2016-09-02T20:18:33.8040974Z Applying Command Line Items
2016-09-02T20:18:33.8040974Z Checking for identical databases
2016-09-02T20:18:33.8040974Z Creating SQL
2016-09-02T20:18:33.8040974Z Inserting static data SQL into deployment script
2016-09-02T20:18:33.8040974Z Deploying changes (from DB1 to DB2)
2016-09-02T20:18:33.8040974Z Summary Information
2016-09-02T20:18:33.8040974Z ===============================================================================
2016-09-02T20:18:33.8040974Z DB1 = state
2016-09-02T20:18:33.8040974Z DB2 = STE_NGTESTDB02.FITDB
2016-09-02T20:18:33.8040974Z Object type Name Records DB1 DB2
Not sure what we're doing wrong.
Thanks!
Craig
The script:
/* Use this migration script to make data changes only. You must commit any additive schema changes first. Schema changes and migration scripts are deployed in the order they're committed. */ UPDATE PEPFAR.RefReportingCycle SET IsLightCycle = 1 WHERE Name = '2016 COP' GO
Using TFS Build vNext task to "sync" to a target (test) database. In the build logs I see the following (and in the database on the target machine, I don't find a RedGateLocal.DeploymentMetadata table). Note that the SQL file referenced is indeed the migration script's file:
2016-09-02T20:18:19.5026163Z Executing the powershell script: C:agent2 asksRedgateSqlCiBuild1.0.21SqlCiTask.ps1
2016-09-02T20:18:19.6432189Z Entering script SqlCiTask.ps1
2016-09-02T20:18:33.7882271Z sqlCI.exe -- Red Gate's SQL Continuous Integration v2.1.33.387
2016-09-02T20:18:33.7882271Z STARTING: Updating database
2016-09-02T20:18:33.7882271Z Starting: C:Program Files (x86)Red GateDLM Automation Suite 1SCSQLCompare.exe /include="Identical" /include="StaticData" /scripts1="C:Usersdeployer.saAppDataLocalTempSQL CIcszpz2yr.v55dbstate" /exclude="User,Queue,Role" /server2="STE_NGTESTDB02" /database2="FITDB" /synchronize /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseCompatibilityLevel,UseMigrationsV2,IgnoretSQLt"
2016-09-02T20:18:33.7882271Z SQL Release: activated, edition: standard, serial number: 610-001-266071-3B82
2016-09-02T20:18:33.7882271Z SQL Compare Command Line V11.6.11.2463
2016-09-02T20:18:33.7882271Z ==============================================================================
2016-09-02T20:18:33.7882271Z Copyright Copyright c 1999 - 2016 Redgate Software Ltd
2016-09-02T20:18:33.7882271Z Warning: Could not find the file 'RedGateDatabaseInfo.xml' in the scripts folder
2016-09-02T20:18:33.7882271Z 'C:Usersdeployer.saAppDataLocalTempSQL CIcszpz2yr.v55dbstate': the
2016-09-02T20:18:33.7882271Z default settings will be used instead.
2016-09-02T20:18:33.7882271Z Registering data sources
2016-09-02T20:18:33.7882271Z Warning: Ignored statement found in file
2016-09-02T20:18:33.8040974Z C:Usersdeployer.saAppDataLocalTempSQL
2016-09-02T20:18:33.8040974Z CIcszpz2yr.v55dbstateDatabaseCustom Scripts2016-09-02-151557 hd
2016-09-02T20:18:33.8040974Z userMigrationScript.sql at line 6
2016-09-02T20:18:33.8040974Z Creating mappings
2016-09-02T20:18:33.8040974Z Comparing
2016-09-02T20:18:33.8040974Z Applying Command Line Items
2016-09-02T20:18:33.8040974Z Checking for identical databases
2016-09-02T20:18:33.8040974Z Creating SQL
2016-09-02T20:18:33.8040974Z Inserting static data SQL into deployment script
2016-09-02T20:18:33.8040974Z Deploying changes (from DB1 to DB2)
2016-09-02T20:18:33.8040974Z Summary Information
2016-09-02T20:18:33.8040974Z ===============================================================================
2016-09-02T20:18:33.8040974Z DB1 = state
2016-09-02T20:18:33.8040974Z DB2 = STE_NGTESTDB02.FITDB
2016-09-02T20:18:33.8040974Z Object type Name Records DB1 DB2
Not sure what we're doing wrong.
Thanks!
Craig
Comments
It looks like the build step is building the parent folder of the database scripts, which seem to be in the "Database" subfolder. If you build the "Database" subfolder instead of its parent then it should work correctly when you try and sync.
Does that help?
Redgate Software
Thanks so much for the quick reply!
FYI, the TFS Build definition we are using is one we've been using for a year now without any (well, many
Thanks again,
Craig
I'm pleased to hear that everthing is now working. You were actually building the incorrect folder in the first place, but because of how the build process works you were able to get away with it! The new migration scripts feature in version 5 of SQL Source Control adds a "Custom Scripts" subfolder - because it was deeper in the directory tree than was expected, it was being treated as a folder of database objects rather than migration scripts.
Redgate Software