Can sqlcompare commit a brand new database to svn via cli?

jschwarz

As part of our new database creation process, we would like to automate the sourcing of new databases so that it can be done as part of an automated workflow. Here is the basic command I've tried and the unsuccessful results. I have tried many variations on this with the same results...

U:\>E:\RedGate\SQLCompare11\sqlcompare.exe /Server1:TSTDBATEST /Database1:DbaTesting2 /sourcecontrol2 /Revision2:HEAD /ScriptsFolderXML:F:\MSSQL\SQLDeployment\SQLSourceXML\Dbatesting2SourceXMLLink.txt /ignoreparsererrors /v /sync /force
SQL Compare: activated, edition: professional, serial number:
SQL Compare Command Line V11.4.10.109
======================================================================================================================================================================================================
Copyright Copyright c 1999 - 2016 Red Gate Software Ltd

Argument /server1 has value 'TSTDBATEST'
Argument /database1 has value 'DbaTesting2'
Argument /sourcecontrol2 has value 'True'
Argument /revision2 has value 'HEAD'
Argument /scriptsfolderxml has value 'F:\MSSQL\SQLDeployment\SQLSourceXML\Dbatesting2SourceXMLLink.txt'
Argument /ignoreParserErrors has value 'True'
Argument /verbose has value 'True'
Argument /synchronize has value 'True'
Argument /force has value 'True'

Comparing database TSTDBATEST.DbaTesting2 with database ....
Registering data sources
Error: Comparison of 'TSTDBATEST.DbaTesting2' and '.' failed: URL 'https://svn.artisanpartners.com/svn/Databases/Dbatesting2' doesn't exist

I've read a number of threads regarding similar errors but have not found a definitive answer to whether this can be done, and if so how to do it successfully.

I have also seen threads where people solved the issue by caching SharpSVN credentials, and my attempt to do the same via powershell results in the SharpSVN successfully downloading the folder in question, but then the sqlcompare job ends with the same error. Perhaps powershell isn't a viable tool for this, but I have no C# skills. Here is my powershell code that I used to test...

### Include Scripts ###
Write-Host "Loading Included Scripts"

if (!(Test-Path "F:\MSSQL\SQLDeployment\Powershell\SharpSvnFuntions.ps1")){Write-Error "Include Script Not Found: F:\MSSQL\SQLDeployment\Powershell\SharpSvnFuntions.ps1"; exit script}
. "F:\MSSQL\SQLDeployment\Powershell\SharpSvnFuntions.ps1"

Write-Host "Starting Checkout"

$CheckoutFolder = "F:\MSSQL\SQLDeployment\SvnLocal\DbaTesting2"

# Get SVN Path from Config File
$SVNPath = "DbaTesting2"

# Download from SVN
if (Test-Path $CheckoutFolder){
Remove-Item $CheckoutFolder -Force -recurse | Out-Null
}

New-Item $CheckoutFolder -ItemType Directory | Out-Null

Write-Host "Downloading From Subversion" -ForegroundColor Magenta
Checkout-Folder $SVNPath $CheckoutFolder
Write-Host "Done"

$exe="E:\RedGate\SQLCompare11\sqlcompare.exe"
$params="/server1:tstdbatest /db1:DbaTesting2 /sourcecontrol2 /revision2:HEAD /sfx:`"F:\MSSQL\SQLDeployment\SQLSourceXML\Dbatesting2SourceXMLLink.txt`""
invoke-expression "& $exe $params"

Here's the results where the SharpSVN download works, but the sql compare still fails...

PS F:\MSSQL\SQLDeployment\Powershell> F:\MSSQL\SQLDeployment\Powershell\SharpSvnConnector.ps1
Loading Included Scripts
Starting Checkout
Downloading From Subversion
https://svn.artisanpartners.com/svn/Databases/DbaTesting2...Done
Done
SQL Compare: activated, edition: professional, serial number:
SQL Compare Command Line V11.4.10.109
==============================================================================
Copyright Copyright c 1999 - 2016 Red Gate Software Ltd

Registering data sources
sqlcompare.exe : Error: Comparison of 'tstdbatest.DbaTesting2' and '.' failed: URL
At line:1 char:1
+ & E:\RedGate\SQLCompare11\sqlcompare.exe /server1:tstdbatest /db1:DbaTesting2 /s ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (Error: Comparis...'.' failed: URL:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError

'https://svn.artisanpartners.com/svn/Databases/Dbatesting2' doesn't exist

way0utwest

I think you have an issue in your SQLCompare command. I see

/sfx:`"F:\ ...

but then I see
txt`""

My guess if you've mismatched your single and double quotes here. You have single double, but then single double again, when I think it should end with double single quote.

jschwarz

I opened this same question as a support ticket, and although RedGate does not have a supported method of making this happen, they did direct me to this link https://alessandroalpi.blog/2016/06/28/automatically-link-databases-to-red-gate-sql-source-control/
I've seen several threads in the forum asking for the same or similar, but I haven't seen anyone pass along this link yet. I am adding it here for the benefit of everyone seeking to do the same.
I will be trying out this method today and will report back the results.