June 25, 2018 at 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 ?
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
June 25, 2018 at 4:43 am
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
July 4, 2018 at 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.
July 5, 2018 at 9:22 am
andycadley - Wednesday, July 4, 2018 2:45 PMMost 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;
July 6, 2018 at 12:08 pm
vsamantha35 - Monday, June 25, 2018 4:08 AMHi 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply