Scripting Azure database

  • Roust_m

    SSCoach

    Points: 17364

    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 4 months, 1 week ago by  Roust_m.
  • Site Owners

    SSC Guru

    Points: 80380

    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