Taking DB backup in Azure Pipeline

• 
  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

  Kendra_Little June 24, 2020 08:31

  - 0 +
• 
  Hi Kendra ,
  
  Could you please provide if any detailed documentation with examples for taking backup in azure pipeline
  
  Regards.
  Manisankar

  Manisankar November 23, 2020 10:03

  - 0 +

Add comment

Please sign in to leave a comment.