How to create maintenance job for specific Azure SQL database?

How it works
1

Hello,

I am trying to run simple sp_updatestats on all databases in my Azure SQL server. Nevertheless I’m unable to select which database to execute the command on, I can only specify the server name. Also I cannot call USE command to change to a specific database.

Question: So how is it possible to run certain SQL command on a specific database in Azure SQL server?

2

Hi Ales_Sturala,

Yes, you can do it, just use the following syntaxsql: USE { database_name }.

If you have any other questions, please let us know.

Thank you.

3

Hi Alexander,

I mentioned in the post that it’s not possible to run USE command on Azure SQL so that is not an option. Basically the maintenance job as far as I know can be run only on the master database.

This is what you get when you try to run USE in the maintenance job.

  • 14:33:59 : Starting maintenance job “tcdev - sp_updatestats”. Server “DESKTOP-F8RTRFJ”. Account “SYSTEM@WORKGROUP”. App v.“12.6.3”.
  • 14:33:59 : Running SQL maintenance script.
  • 14:33:59 : ERROR: Job execution error: One or more errors occurred. > USE statement is not supported to switch between databases. Use a new connection to connect to a different database. Server name: “DESKTOP-F8RTRFJ”.
  • 14:34:00 : Deleting temporary files.
  • 14:34:00 : ERROR: Job “tcdev - sp_updatestats” finished with “1” errors.
4

Hi Ales_Sturala,

Oh, you are right. Sorry, but there is no way to do it via SQLBackupAndFTP, perhaps that option will be added in the future.

Thank you and sorry for the inconvenience.

5

Thank you for the reply. It would be really useful to have it working also for Azure SQL! Similar to what you do in backup where you can select database(s) to backup in a separate input.

6

Hi Ales_Sturala,

Thank you for the clarification. Your request has been forwarded to our dev team for consideration.

Thank you and sorry for the inconvenience.

7

Hi,

Do you have any update on this? Is this going to be solved? It shouldn’t be that difficult since you’re already able to do this with backups.

8

Hi Ales_Sturala,

Thank you for getting in touch with us.

Sorry, but for now we don’t plan to add this feature.

Thank you and sorry for the inconvenience.

9

Just wanted to bump that this would be a nice feature. It’s borderline a stand alone product for Azure SQL.

10

Hi Andrew_Dakin,

Thank you for your request!

Thank you!