Scripting Azure database

  • Hi,

    I used to script databases in traditional SQL Server instances using automated jobs with powershell job steps.  An example of such step is below.

    In Azure elastic jobs Powershell commands are not supported, only TSQL.  What are my options to automatically backup database schema?

    Thanks.

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'script databases',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'PowerShell',

    @command=N'#Stop script on any error

    $erroractionpreference = "Stop"

    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

    $Srv = ''$(ESCAPE_SQUOTE(SRVR))''

    SL SQLSERVER:\SQL\"$Srv"\Databases

    $s = new-object (''Microsoft.SqlServer.Management.Smo.Server'') "$Srv"

    $dbs=$s.Databases

    $so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions

    $so.IncludeIfNotExists = 1

    $so.ExtendedProperties = 1

    # $so.Permissions = 1

    $so.IncludeDatabaseContext = 1

    # $so.WithDependencies = 1

    $Query = "select name from master.sys.databases (nolock) where database_id > 5"

    $DBList = Invoke-Sqlcmd -query $Query

    foreach($item in $DBList) {

    $DB = $item.name

    $File = "01-DB.sql"

    $script = $dbs["$DB"].Script($so)

    $script = $script -replace "''", "''''"

    $Query = "INSERT INTO [MySupportDB].[dbo].[DBScripts]([DBName], [ScriptName], [ScriptDate], [ScriptText]) VALUES (''$DB'',''$File'',''$timestamp'',''$script'')"

    Invoke-Sqlcmd -query $Query

    }'

    • This topic was modified 2 years, 4 months ago by  Roust_m.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply