Taking DB backup in Azure Pipeline

Kattesang

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.

Kendra_Little

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

Manisankar

Hi Kendra ,

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

Regards.
Manisankar