1

So someone dropped a column in a Azure sql db. How to show the table schema before the change? Restore to a previous version is not a option as that would override everything.

4 Answers 4

2

You should had enabled Azure SQL Auditing.

Here you can find all about it.

https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview#subheading-1

Now you have to restore the database as a new database with an alternative name and the examine te differences in table definition.

2

How to show the table schema before the change?

Restore a copy of the database from backup, then either:

  • Replace the existing table with the restored one (which will, as you say, lose other data in the process), or
  • Add the column back into the table, update the data for the rows that you have from the backup, then work out how to fill in the data where its [still] missing.

Once you've done all of that (or, possibly even before), look at locking down the security on your database(s). Somebody dropped a column out of that table; possibly the most disruptive thing they can do (recovering a completely "dropped" table is nothing by comparison).

This must not be possible in Production. Not without [a lot of] people:

  • knowing that somebody wants to do it,
  • knowing when that somebody wants to do it,
  • having the opportunity to say "No!",
  • knowing when that somebody starts doing it,
  • knowing when that somebody finishes doing it, and
  • receiving confirmation that the doing was successful.

In a nutshell, it's called Change Control.

Longer term, you need to re-examine who/what is running with these permissions in Production:

  • Applications - go back and challenge the "need" for it.
    You now have empirical evidence that doing so is dangerous and disruptive to the Business (i.e. doing so has caused a Production Incident and that recovering from that Incident has cost them money). This isn't just some "possibility" any more - it's happened.
  • People (often Developers) - Revoke their permissions. Right Now.
    Don't ask. Don't discuss. Just do it. Then let them try to "justify" why they need them back.
1

Source control ? Failing that I think you will need to restore a database backup to a new name

easiest via the Azure Portal - don't forget to supply a NEW name for the database so that you don't overwrite the existing one.

https://azure.microsoft.com/en-us/blog/azure-sql-database-point-in-time-restore/

1

So, two questions here. First is the restore. You can restore a SQL Database right now. As the documentation states, you can't, as in can not, overwrite an existing database. The process of a restore in Azure SQL Database requires that you create a copy of the database. You don't overwrite your existing database at all. So immediately do that restore. You can get your table and the data back. That's step 1.

Step 2, question 2, who did this? Sadly, you can't tell now. However, you can set up an extended events session that will capture structural changes. Further, you can filter who captures those changes for. Here's an example session. You only have to modify the WHERE clause to filter for the logins that are allowed to make changes on your database:

CREATE EVENT SESSION UnauthorizedObjectChanges
ON SERVER
    ADD EVENT sqlserver.object_altered
    (SET collect_database_name = (1)
     ACTION
     (
         sqlserver.client_app_name,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text
     )
     WHERE (
               package0.equal_uint64(ddl_phase, (1))
               AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'tempdb')
               AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'master')
               AND package0.not_equal_uint64(object_type, (21587))
               AND sqlserver.server_principal_name <> N'LoginName'
           )
    ),
    ADD EVENT sqlserver.object_created
    (SET collect_database_name = (1)
     ACTION
     (
         sqlserver.client_app_name,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text
     )
     WHERE (
               package0.equal_uint64(ddl_phase, (1))
               AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'tempdb')
               AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'master')
               AND package0.not_equal_uint64(object_type, (21587))
               AND sqlserver.server_principal_name <> N'LoginName'
           )
    ),
    ADD EVENT sqlserver.object_deleted
    (SET collect_database_name = (1)
     ACTION
     (
         sqlserver.client_app_name,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text
     )
     WHERE (
               package0.equal_uint64(ddl_phase, (1))
               AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'tempdb')
               AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'master')
               AND package0.not_equal_uint64(object_type, (21587))
               AND sqlserver.server_principal_name <> N'LoginName'
           )
    )
    ADD TARGET package0.ring_buffer

Between those two, you should have what you need to recover your data right now, and to protect the system better in the future. I also agree with the other answers, clean up your security.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.