Scripting Azure database

• Roust_m

  SSCoach

  Points: 17408

  More actions

  June 12, 2019 at 7:36 am

  #3652619

  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 years, 10 months ago by  Roust_m.

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  June 13, 2019 at 8:10 am

  #3653107

  Thanks for posting your issue and hopefully someone will answer soon.

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