DDL statements getting rollbacked

  • Hi All,

    Can we rollback DDL statements as well (create, drop, alter,truncate etc...)????

    As per my knowledge in sql 2000 we ca'tn do this right? I know DDL stmts are self-commit/auto commit statements.  From when or which version rollbacking is available for DDL statenments ?

    SQL Server Version
    ===================
    select @@version
    --Microsoft SQL Server 2017 (RTM-GDR) (KB4057122) - 14.0.2000.63 (X64)   Dec 22 2017 16:01:23   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 16299: )

    begin tran
     

            create table t1
            (id int,
            name varchar(100)
            )

            select * from t1

            alter table t1
            add sal int not null;

            select * from t1

    rollback;

    select * from t1;

    /*
    Msg 208, Level 16, State 1, Line 34
    Invalid object name 't1'.

    */

    Thanks,

    Sam

  • The easiest way to find this out is to test:

    USE Sandbox;
    GO

    BEGIN TRANSACTION CreateTable;

        CREATE TABLE dbo.test (ID int);
     
        SELECT *
        FROM dbo.test;

    ROLLBACK TRANSACTION CreateTable;

    GO
    --This fails
    SELECT *
    FROM dbo.test;
    GO
    --Try an ALTER

    CREATE TABLE dbo.test (ID2 int);

    BEGIN TRANSACTION AlterTable;

        ALTER TABLE dbo.Test ADD String varchar(10);

        --2 columns
        SELECT *
        FROM dbo.test;

    ROLLBACK TRANSACTION AlterTable;

    GO
    --Only 1 column now.
    SELECT *
    FROM dbo.test;
    GO
    --Cleanup
    DROP TABLE dbo.test;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Most DDL statements can be rolled back in 2005, though I don't think TRUNCATE can (at least not without jumping through some hoops first). I have an inkling that you could do it in 2000 as well but I don't have access to anything that old to try it on any more.

  • andycadley - Wednesday, July 4, 2018 2:45 PM

    Most DDL statements can be rolled back in 2005, though I don't think TRUNCATE can (at least not without jumping through some hoops first). I have an inkling that you could do it in 2000 as well but I don't have access to anything that old to try it on any more.

    Truncate works too:


    CREATE TABLE TRUNCATE_TEST
    (
        ID INT IDENTITY(1,1),
        SOME_TEXT VarChar(30)
    );

    INSERT INTO TRUNCATE_TEST (SOME_TEXT)
    SELECT TOP (30) Name FROM Sys.Objects;

    GO

    SELECT Count(*) AS Record_Count_1 FROM TRUNCATE_TEST;

    BEGIN TRANSACTION

        TRUNCATE TABLE TRUNCATE_TEST;
        
        SELECT Count(*) AS Record_Count_2 FROM TRUNCATE_TEST;

    ROLLBACK;

    SELECT Count(*) AS Record_Count_3 FROM TRUNCATE_TEST;

    DROP TABLE TRUNCATE_TEST;

  • vsamantha35 - Monday, June 25, 2018 4:08 AM

    Hi All,

    Can we rollback DDL statements as well (create, drop, alter,truncate etc...)????

    As per my knowledge in sql 2000 we ca'tn do this right? I know DDL stmts are self-commit/auto commit statements.  From when or which version rollbacking is available for DDL statenments ?

    All versions. It has been possible to roll back DDL statements in SQL Server for a very long time, and that goes for any DDL statement.

    May have been different back 6.0 or 4.2

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply