SQL Server - Can DDL commands be rolled back? [duplicate]

Question

I found similar queries on stack overflow but was not able to find exact answer.

When I use DDL commands like drop and truncate on SQL Server 2012, I am able to rollback the changes? In theory, I have always read that you can rollback DML commands but not DDL as they are not logged.

My question is, if DDL commands can also be rolled back? Or this some special feature or settings change in my SSMS which is causing it.

What about the other SQL platforms like Oracle and Postgres? Can we also rollback DDL commands on them?

Answer 1

DDL rolls back.

Example:

BEGIN TRANSACTION

CREATE TABLE a ( Id INT NOT NULL )

SELECT * FROM a -- returns an empty row

ROLLBACK TRANSACTION

SELECT * FROM a -- throws an error, object does not exist

I always include my CREATEs, ALTERs, DROPs and TRUNCATEs inside transactions for that exact reason. That way, if there is an error, I don't get some objects, but not others. Test the DDL you have questions about.

Answer 2

You can use Begin Transaction Block and Rollback - Commit as shown below:

BEGIN TRANSACTION  
       INSERT INTO YourTable VALUES(1), (2);  
ROLLBACK TRANSACTION;  
  
INSERT INTO YourTable VALUES(3),(4);  
  
SELECT [value] FROM YourTable;  
  
DROP TABLE YourTable; 

COMMIT TRANSACTION;

and also if you want to do some actions (DDL commands or DML commands) you can turn off Auto commit

First, create a new query page and then, from the menu items: Query\ Query options

Set IMPLICIT_TRANSACTIONS to True

And whenever you did your job, either you can commit your transactions or rollback by commit \ rollback command or by closing page and confirm\reject the transactions

But after that, if your transactions applied to your DB and tables, You can rollback drop table or truncate table with the Microsoft SQL recovery tool - EaseUS MS SQL Recovery

you will be able to recover the truncated table or recover the table after DROP. EaseUS MS SQL Recovery is a powerful tool to recover deleted or corrupted SQL database data. It can restore deleted SQL data in most situations and repair the corrupted database (the MDF and NDF files).