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

Taking DB backup in Azure Pipeline

Is RedGate supports for taking DB backup in pipeline? If possible, how to take it without data and taking only specific objects like Store Procedures and Views?

Thanks.


Tagged:

Answers

  • Options
    Kendra_LittleKendra_Little Posts: 139 Gold 3
    edited June 24, 2020 8:40AM
    Hello,

    To capture the schema for specific objects without data, you can use the SQL Compare command line. 

    Here's an example command. This example compares a database to the "schema-model" folder in the release artifact for a SQL Change Automation project. In an Azure DevOps pipeline, you can set up a step after creating a release artifact (but before deployment) and run this step, and it will write out a rollback script for the stored procedures, views, and functions which the release is modifying:

    <div>$SQLCompare="${env:ProgramFiles(x86)}\Red&nbsp;Gate\SQL&nbsp;Compare&nbsp;14\sqlcompare.exe"#&nbsp;full&nbsp;path</div><div>$MyServerInstance="InstanceName"</div><div>$MyDatabase="TargetDatabase"</div><div>$SchemaModelPath="C:\PathToReleaseArtifact\Project\Schema-Model\"&nbsp;</div><div>$OutputFile&nbsp;=&nbsp;"C:\PathToDesiredOutput\RollbackTest.sql"</div><br><div>$AllArgs&nbsp;=&nbsp;@("/server1:$MyServerInstance",&nbsp;"/database1:$MyDatabase",</div><div>"/scripts2:$SchemaModelPath",</div><div>"/Assertidentical",</div><div>"/include:StoredProcedure",</div><div>"/include:View",</div><div>"/exclude:View:__MigrationLogCurrent",</div><div>"/include:Function",</div><div>"/Options:AddDatabaseUseStatement,DecryptPost2KEncryptedObjects,DoNotOutputCommentHeader,IgnoretSQLt,IgnoreWhiteSpace,IgnoreWithElementOrder,UseCompatibilityLevel,IgnoreUsersPermissionsAndRoleMemberships",</div><div>"/ScriptFile:$OutputFile",</div><div>"/force"&nbsp;#&nbsp;This&nbsp;makes&nbsp;it&nbsp;overwrite&nbsp;the&nbsp;file&nbsp;if&nbsp;it&nbsp;is&nbsp;present,</div><div>"/LogLevel:Verbose"&nbsp;#&nbsp;%localappdata%\Red&nbsp;Gate\Logs</div><div>)</div><div>&$SQLCompare&nbsp;$AllArgs</div>


    Some notes:
    • In an Azure DevOps pipeline, you will probably need to use the ReleaseName variable for the path to the release artifact
    • If you are using a SQL Source Control project, you can do a similar pattern, but just point it to the folder holding your state in the release artifact
    • If you want to script out ALL of these types of objects, not just those modified in a release, you can compare the database to an empty folder

    Coincidentally, I'm working on some improved documentation and examples for this right now :)

    Hope this helps and let me know if you have questions,

    Kendra
  • Options
    ManisankarManisankar Posts: 1 New member
    edited November 23, 2020 10:03AM
    Hi Kendra ,

    Could you please provide if any detailed documentation with examples for taking backup in azure pipeline

    Regards.
    Manisankar
Sign In or Register to comment.