SQL Server - Can DDL commands be rolled back?

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?

How about checking the official MS docs?? Seems like a good idea - here's [`TRUNCATE TABLE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15) and there's a *Note: A TRUNCATE TABLE operation can be rolled back.* - so obviously - yes, those DDL commands *can be* rolled back as needed — marc_s, Dec 26 '20 at 07:32

jim · Answer 1 · 2020-12-26T13:18:24.090

2

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.

edited Dec 26 '20 at 13:18

answered Dec 26 '20 at 12:08

jim

401
4
10

@user2611539 . . . Just to be clear, this answer is for SQL Server. I'm upvoting because that is how I interpret your question. Asking the same question about multiple databases is too broad for one question (in my opinion), particularly one about a topic such as transaction management that differs significantly across databases. – Gordon Linoff Dec 26 '20 at 15:00

Hesam Akbari · Answer 2 · 2020-12-27T09:47:20.480

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).

SQL Server - Can DDL commands be rolled back?

2 Answers2