SQL Compare Source Control Problem
SteveGTR
Posts: 91
I'm developing a database drift process that uses snapshots created by SQL Compare version 10.7.0.18.
The process uses PowerShell and goes like this:
1) Create snapshot by getting latest from source control (TFS)
& sqlcompare /force /makesnapshot:c: empmydb.snp /server1:. /database1:mydb /Revision1:HEAD
2) I run a SQL script to create an empty database on another SQL instance
& sqlcmd -S .SQLInstance -E -i c: empcreatemydb.sql
3) Generate a change script using the snapshot from step #1 and the empty database from step #2
& sqlcompare /force /scriptfile:c: empmydb.sql /snapshot1:c: empmydb.snp /server2:.SQLInstance /database2:mydb
4) If I run the script generated in step #3 in SSMS, it throws an error because it attempts to create a view that is dependent on a table that isn't created until later in the script.
Note, if I generate the snapshot using a database, there doesn't appear to be any dependency problems. For now, I'm going back to using our development database for the snapshot generation, but it would be nice to just generate the script from source control.
Thanks,
Steve
The process uses PowerShell and goes like this:
1) Create snapshot by getting latest from source control (TFS)
& sqlcompare /force /makesnapshot:c: empmydb.snp /server1:. /database1:mydb /Revision1:HEAD
2) I run a SQL script to create an empty database on another SQL instance
& sqlcmd -S .SQLInstance -E -i c: empcreatemydb.sql
3) Generate a change script using the snapshot from step #1 and the empty database from step #2
& sqlcompare /force /scriptfile:c: empmydb.sql /snapshot1:c: empmydb.snp /server2:.SQLInstance /database2:mydb
4) If I run the script generated in step #3 in SSMS, it throws an error because it attempts to create a view that is dependent on a table that isn't created until later in the script.
Note, if I generate the snapshot using a database, there doesn't appear to be any dependency problems. For now, I'm going back to using our development database for the snapshot generation, but it would be nice to just generate the script from source control.
Thanks,
Steve
