\nWhat I have tried so far that is almost satisfactory is detailed in this article summary.
\nSpecifically, the section below where the following guidance is given:
\n\n\nWhen a folder is under source control, the extracted object definitions would show differences in source control tooling. By using SqlPackage to generate the files and check for differences in source control, you can automate the process of importing changes from a database into a SQL project file set.
\n
The example commands it provides simplified, work out to something like the following:
\nrm -rf MyDatabaseProject\nsqlpackage /Action:Extract /SourceConnectionString:{connection string for MyDatabase} /TargetFile:MyDatabaseProject /p:ExtractTarget=SchemaObjectType\ngit status --porcelain | wc -l\ndotnet new sqlproj -n MyDatabaseProject -o MyDatabaseProject\n
\nRunning those commands works very well, with one significant issue: when committing to source control, the diff shows that indexes and other items were changed that were not changed. The reason it shows them is because the extracted files from the sqlpackage command are inconsistent in the way certain items like indexes are ordered. As a result, despite there being no change, the reordering makes it seems as though there was one. This is bad because it makes it much more cumbersome to find actual changes.
\nHere is an example of how this looks in the diff:\n
Can this problem be fixed?
\n","author":{"@type":"Person","name":"person0"},"upvoteCount":0,"answerCount":1,"acceptedAnswer":null}}Reputation: 1388
This Microsoft article details how to both create and update a sql project from a database when changes are made to the database that don't yet exist in the project. I would like to accomplish this via a command line prompt.
So far, the only commands I have found exist to either update the database from the project or to create a brand-new project from scratch; however, that is not the direction I am looking to go.
How can I accomplish this?
UPDATE:
What I have tried so far that is almost satisfactory is detailed in this article summary.
Specifically, the section below where the following guidance is given:
When a folder is under source control, the extracted object definitions would show differences in source control tooling. By using SqlPackage to generate the files and check for differences in source control, you can automate the process of importing changes from a database into a SQL project file set.
The example commands it provides simplified, work out to something like the following:
rm -rf MyDatabaseProject
sqlpackage /Action:Extract /SourceConnectionString:{connection string for MyDatabase} /TargetFile:MyDatabaseProject /p:ExtractTarget=SchemaObjectType
git status --porcelain | wc -l
dotnet new sqlproj -n MyDatabaseProject -o MyDatabaseProject
Running those commands works very well, with one significant issue: when committing to source control, the diff shows that indexes and other items were changed that were not changed. The reason it shows them is because the extracted files from the sqlpackage command are inconsistent in the way certain items like indexes are ordered. As a result, despite there being no change, the reordering makes it seems as though there was one. This is bad because it makes it much more cumbersome to find actual changes.
Here is an example of how this looks in the diff:
Can this problem be fixed?
Upvotes: 0
Views: 98
Reputation: 31
If RedGate SQL Compare is an option, then it can be used to update a SQL Project from a database through the command line https://documentation.red-gate.com/sc/using-the-command-line
Here's an example using powershell:
$sqlComparePath = "${env:ProgramFiles(X86)}\Red Gate\SQL Compare 15\SQLCompare.exe"
$server='ServerName'
$database='DatabaseName'
$projectPath='Path to SQL Project'
$ErrorActionPreference = "silentlycontinue" #this is needed to prevent an error when no changes exist
Invoke-Command -ScriptBlock {& $sqlComparePath /Server1:$server /Database1:$database /Scripts2:$projectPath /options:Default,IgnoreWhiteSpace,IgnoreExtendedProperties,IgnoreWithNocheck,IgnoreQuotedIdentifiersAndAnsiNullSettings,IgnoreStatistics,IgnoreCertificatesAndCryptoKeys /sync}
$ErrorActionPreference = "Stop"
Upvotes: 0