RollBack Process

[Lost User]

Hello Everybody, Is it possible to Rollback Table in SQL Server 2005 if it is Truncated? Thanks

If you can think then I Can.

[R Giskard Reventlov]

No: see here: TRUNCATE TABLE (Transact-SQL)[^]. From the page: "The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log."

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me

[Wendelius]

Hi, If you mean that can you rollback the truncation of a table if it's done in a tranaction: Yes. You can test this with a small test case:

-- Table creation and filling
CREATE TABLE TruncTest (
Column1 int);

BEGIN
DECLARE @cnt INT;
SET @cnt = 0;
WHILE @cnt < 100 BEGIN
INSERT INTO TruncTest VALUES (@cnt);
SET @cnt = @cnt + 1;
END;
END;

SELECT COUNT(*) AS Rows FROM TruncTest;

-- Result:
-- Rows
-- 100

--test truncation
BEGIN TRANSACTION;
TRUNCATE TABLE TruncTest;
SELECT COUNT(*) AS Rows FROM TruncTest;

-- Result:
-- Rows
-- 0

ROLLBACK;

SELECT COUNT(*) AS Rows FROM TruncTest;

-- Result:
-- Rows
-- 100