Search code examples
sqlsql-serverazureazure-sql-database

Store Execution history of CREATE, TRUNCATE and DROP statements in Azure SQL Database

I am working with an Azure SQL Database. I researched a lot but I can't find a way to store all the CREATE, TRUNCATE and DROP queries executed on my database. The Query Store only stores the SELECT etc... queries so that is also of no use.

Does anyone know a way to do this?

Solution

  • AFAIK Azure SQL Database does not provide a built-in mechanism to capture all actions performed on the database, including data definition language (DDL) statements like CREATE, TRUNCATE, and DROP queries, you can use Azure SQL Auditing and Log Analytics to capture and analyze this information. To enable Audits on server level, go to auditing option in server and click on enable and select where need to store the logs like storage account, log analytics, event hubs and click on save.

    enter image description here

    After that go to database level and select auditing and follow above procedure to enable audits.

    enter image description here

    I executed create and drop command on database

    create table emp(ID int,Name varchar(20))
Drop table emp

    These logs are stored in selected storage account.

    enter image description here