What are the challenges you face when working across database platforms? Take the survey

Using without source control

samjudsonsamjudson Posts: 16
edited October 31, 2014 11:43AM in DLM Automation
Probably a silly thing to do, but is there any reason why you couldn't just use SQL Release based solely on two databases, i.e. the Schema of your 'Dev' database IS the source controlled version?

We do have our databases under source control, but without SQLCi I have trouble actually getting that data out of source control (I'm probably being thick here, but I've never managed to get my head around this bit).

Any suggestions would be useful.



  • Options
    Chris LambrouChris Lambrou Posts: 25 Bronze 3
    edited April 24, 2015 8:05AM
    Hi Sam,

    Example 1 of the New-DatabaseRelease cmdlet shows exactly this use case (to view it, type Get-Help New-DatabaseRelease -Examples from a PowerShell prompt). The example generates a new Database Release containing SQL to apply the schema of a 'Test' database to a 'Staging' database.
    # Define the connection details to the 'Test' and 'Staging' databases.
    $staging = New-DatabaseConnection -ServerInstance 'staging01sql2012' -Database 'Staging' -Username 'sa' -Password 'P@ssw0rd'
    $test = New-DatabaseConnection -ServerInstance 'test01sql2012' -Database 'Test' -Username 'sa' -Password 'P@ssw0rd' 
    # Create a Database Release that can be used to update the 'Staging' database schema to match that of the 'Test' database.
    $release = New-DatabaseRelease -Source $test -Target $staging
    # And finally apply the release to the 'Staging' database.
    $release | Use-DatabaseRelease -DeployTo $staging

    However, it's not really an intended use case, and there are some drawbacks. For example, a version controlled scripts folder (as used by SQL Source Control) includes additional information about static data that is simply unavailable if using SQL Release to directly sync between two databases. I'd strongly consider using SQL CI, or even working directly with the SQL Source Control scripts folder, rather than a database to act as the source of the new schema to be deployed.

    We're more than happy to help you out if you're struggling to understand how best to get your source controlled database deployed to your production database. You can contact the SQL Release team about this directly at DLMAutomationSupport@red-gate.com
    Chris Lambrou
    Software Engineer, SQL Monitor Team
  • Options
    Thanks for the reply.

    I have found out I can use the scripts folder to do the compare by just checking out of TFS. Now I just have to work out how to sort our databases out - we have a rather complicated set of databases (doesn't everyone I suppose). Each are based on the parent source controller version, but with many subtle differences unfortunately.

  • Options
    Chris LambrouChris Lambrou Posts: 25 Bronze 3
    edited March 13, 2015 5:53AM
    Ah, cool. For the record, you can indeed specify the path of a scripts folder as the -Source parameter to the New-DatabaseRelease cmdlet.
    Chris Lambrou
    Software Engineer, SQL Monitor Team
Sign In or Register to comment.